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

**olives** · 02-16-2013, 02:15 AM

Rick, I like the formula and it's working well, but now I want to move data around and I think the reference to cell A1 is hindering me. Is there any way to make the formula and/or the code more 'generic' and have it search the next two adjacent columns to where I place the formula instead of always placing the number I want compared to in A1? I know I said A1 above, but it'd be nice to be bale to place the formula anywhere and have it search in the next two columns, as above, first to check if the number is below the comparison value and then to check for thehighest value items... Thanks!

**Rick Rothstein** · 02-16-2013, 03:29 AM

[QUOTE=olives;3609]...but now I want to move data around and I think the reference to cell A1 is hindering me. Is there any way to make the formula and/or the code more 'generic' and have it search the next two adjacent columns to where I place the formula instead of always placing the number I want compared to in A1?
[QUOTE]
Okay, I think this will work the way you want, but note that the argument list to the function is completely different. First I'll give you the code, then I'll explain how to use it.

Code:

Function Largest(CompareValue As Double, TopLeftNumber As Range) As Variant
  Dim X As Long, Index As Long, TestValue As Variant, Source As Variant, Results As Variant
  Source = TopLeftNumber.Resize(Cells(Rows.Count, TopLeftNumber.Column).End(xlUp).Row - TopLeftNumber.Row + 1, 2)
  ReDim Results(1 To UBound(Source), 1 To 1)
  For X = 1 To UBound(Source)
    If Source(X, 1) < CompareValue Then
      Index = Index + 1
      Results(Index, 1) = Source(X, 2)
    End If
  Next
  Largest = WorksheetFunction.Large(Results, Application.Caller.Row - TopLeftNumber.Row + 1)
End Function

The first instance of this UDF must be placed on the same row as the first (topmost) number in your two columns of numbers. So, if your two columns of numbers were in C3:D100, the first instance of the formula must be placed in a cell on Row 3 (it does not have to be adjacent to the two columns of numbers). Now, for the arguments. The first argument is the value you want to compare (what used to be in A1 originally). You can specify it as a constant or a cell reference (but if you specify it as a cell reference, then the row number must be absolute (that is, there needs to be a $ sign in front of the number) so it won't change as the UDF is copied down. The second argument is a reference to the top left cell of the two columns of numbers being compared and the row number must also be absolute (that is, there needs to be a $ sign in front of the number) so it won't change as the UDF is copied down. So, if the two columns of numbers were in C3:D100, and the comparison number were 100, then the formula would look like this...

=Largest(100,C$3)

On the other hand, if your compare value was placed in F2, then the formula would look like this...

=Largest(F$2,C$3)

Note you do not specify the full range on that second argument, only the top left cell address... the code will calculate the row containing the last value for the two columns of numbers on its own.

Does this modification do what you want?

**olives** · 02-17-2013, 11:17 PM

Originally Posted by Rick Rothstein

... Does this modification do what you want?

Ok, I don't know if I'm doing anything wrong, but I set up the function and when I have it read =Largest(D$1, P$1) it does give me the highest value in Column Q but if I change the value in D$1, I'm getting the #VALUE error. I can't tell if it's working ...

I also don't get any values below where I insert the function if I copy it below the first instance. I get the #VALUE error again.

Would you like me to set up and upload a dummy data sheet?...

And, I don't know anything about the functions, but, to make it even more 'portable', I know you have the function default to two contiguous columns, but also be able to have it look something ike this: ?

=Largest(F$2, C$3, G$3)

Meaning:

1. Compare the value in Cell F$2 with all the values in Column C, then for all the values in Column C that are below the value in F$2, go to the same row in Column G, compare those values and give me the highest, etc...?

Where I could designate those columns? Just wondering... I gotta learn how to program! Thanks!