Is there any formula to look for the next value provided by a condition?

[w_k_c]

Hi,

I have lots of data like this:

A B C D E F (Column)
1 X1 John 2 John X1
1 Y1 Peter 2 Peter Y1
1 Z1 Ken 3 Ken Z1
5 X2 John 6 John X2
5 Y2 Peter 7 Peter Y2

My situation is as follows:

In column F, if D1 (i.e. 2) and E1= John, then use D1 to look for the closest value in column A, but not larger than 2. As such, F1 return the result "X1".
if D5 (i.e. 7) and E15 = Peter, then use D5 to look for the closest value in column A, but not larger than 7. As such, F5 return the result "Y2"

Is there any formula to put in columen F for my case?

Thank you All!!

[ragulduy]

=INDEX(B$1:B$5,MATCH(MAX(IF(C$1:C$5=E1,A$1:A$5)*IF(A$1:A$5<=D1,1)),IF(C$1:C$5=E1,A$1:A$5)*IF(A$1:A$5<=D1,1),0))

entered as an array formula (confirm with ctrl+shift+enter) in F1 and copied down (assuming your data is in A1:E5).

[w_k_c]

hi ragulduy

it works! thanks a lot, you save my world!!

But, would you mind to tell/teach me the meaning of "MAX(IF(C$1:C$5=E4,A$1:A$5)*IF(A$1:A$5<=D4,1))" and why A$1:A$5 in the IF function can return a numeric value?

Greatly appreciated!

Ken