VLOOKUP plus IF

[Ducklady]

I would like to identify a "Y" when it shows up in a column, and then compare
a value in one cell (A2) with the four values in the row next to the "Y"
indicator. Can anyone help with this? The "Y" will move up or down within
the column depending on values entered in the worksheet, and I already have
this part of the formula completed. The value in cell A2 will change. If A2
is greater than the number in column D, then D is returned. If A2 is greater
than the number in column E, but not greater than the number in column D,
then E would be returned, etc. V 2003
D E F G
N 50 40 30 20
Y 45 35 30 25
N 35 30 25 20

 

[T. Valko]

Try this array formula** :

Assumes the value in A2 will not be less than the minimum value in the
corresponding row. For example, if A2 = 24 based on your posted sample data
the formula will return #N/A.

=INDEX(INDEX(D2:G4,MATCH("Y",C2:C4,0),0),MATCH(TRUE,A2>=INDEX(D2:G4,MATCH("Y",C2:C4,0),0),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.