Hi All,


I'm not an expert in Excel, and I've been trying to learn how to retrieve the green cell value with a formula.

So basically a formula that says the following: ( please refer to the attached Table)

1) Look for [F2] which is Brand 2
2) Look for the closest or nearest number to [F3] which is 15
3) Retrieve the cross matching exact value which is 5.

I've managed to use a formula to retrieve exact match (if I write 18.9 in [F3] I get the green cell, but if I put a number that is not in column A, the formula gives an N/A)

Would appreciate your help.

Image
Thanks,
Maghraby

Attached Images

Tableeeeee.png (5.8 KB, 18 views)

=IF(F3>MAX(A2:A4),"",INDEX(B2:d4,MIN(IF(A2:A4>F3,R OW(A2:A4)-1)),MATCH(F2,B1:d1,0)))

It is an array formula so when entering remember to hold down Ctrl and Shift before pressing Enter. If you do it correctly Excel will automatically put braces {} around the formula.

XOR: Thank you very much, works perfectly.

Really appreciate your help

Please note that there should not be a space between R and OW.
I don't know why msofficeforums often makes this error even if the formula is correct when posting.