Mutiple lookup, Exact and closest.

[raven_squire]

Hello,

I have some data in a table that looks like this Text, float, Result 1, Result 2

I am hoping that someone can help with a formula that will lookup the row with the closest float where the text matches.
I would say it is a multiple criteria lookup where one criteria is an exact match while the other is the closest match.

Thanks

 

[pgc01]

Hi

With
- the table in A:D
- the text you are looking in G2
- the float you are looking in H2

try for result 1:

=INDEX(C2:C100,MATCH(MIN(IF(A2:A100=G2,ABS(B2:B100-H2))),IF(A2:A100=G2,ABS(B2:B100-H2)),0))
This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER.

 

[raven_squire]

Thank you so much pgc01, this has been a massive help.

 

[pgc01]

You're welcome.