Yes, please! Welcome back! Thanks!Quote:
Originally Posted by Rick Rothstein
Yes, please! Welcome back! Thanks!Quote:
Originally Posted by Rick Rothstein
Give this UDF a try...Quote:
Originally Posted by olives
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...Code: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
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)