Looking for Value in Another Column, Evaluating and Then Looking in Another Column

[olives]

Sorry about my absence...

Yes, please! Welcome back! Thanks!

[Rick Rothstein]

Yes, please! Welcome back! Thanks!

Give this UDF a try...

Function Largest(CompareValue As Double, FirstNumberCell As Range, _
                 Optional SecondNumberCell As Range, _
                 Optional ReferenceColumn As Long = 1) As Variant
  Dim X As Long, Index As Long, TestValue As Variant, RefCell As Range
  Dim Source1 As Variant, Source2 As Variant, Results As Variant
  Application.Volatile
  If SecondNumberCell Is Nothing Then Set SecondNumberCell = FirstNumberCell.Offset(, 1)
  Source1 = FirstNumberCell.Resize(Cells(Rows.Count, FirstNumberCell.Column). _
                                   End(xlUp).Row - FirstNumberCell.Row + 1, 2)
  Source2 = SecondNumberCell.Resize(Cells(Rows.Count, SecondNumberCell.Column). _
                                    End(xlUp).Row - SecondNumberCell.Row + 1, 2)
  ReDim Results(1 To UBound(Source1), 1 To 1)
  For X = 1 To UBound(Source1)
    If Source1(X, 1) < CompareValue Then
      Index = Index + 1
      Results(Index, 1) = Source2(X, 1)
    End If
  Next
  If ReferenceColumn = 1 Then
    Set RefCell = FirstNumberCell
  Else
    Set RefCell = SecondNumberCell
  End If
  Largest = WorksheetFunction.Large(Results, Application.Caller.Row - RefCell.Row + 1)
End Function

Note the argument list has changed. The first argument is the number or reference to a cell containing the number you want to be lower than, the second argument is a reference to the cell containing the first number in the search column, the third argument is a reference to the cell containing the first number in the result column (the source and results lists do not have to both start on the same row), and the last argument is either a 1 or 2 depending on which column's first number the formula is aligned to (1 means the formula is on the same row as the source column's first number and 2 means the formula is on the same row as the result column's first number). The third argument is optional and if omitted, the result column defaults to the cells immediately to the right of the source cells (that makes it function exactly like my first posted UDF). The fourth argument is also optional and defaults to 1 (the source cells). So, assuming this...

A1 - 100
F3:F18 - contains source values to check against A1's value
J3:J18 - contains result values where the largest value will be returned from
M3 -=Largest(A1,F3,J3)

However, for this arrangement...

A1 - 100
F3:F18 - contains source values to check against A1's value
J23:J38 - contains result values where the largest value will be returned from
M23 -=Largest(A1,F3,J23,2)