Combined match and lookup formula

Hi Ian,

Please try this...

in L5

=INDEX($S$5:$AB$13,MATCH(B5,INDEX($S$5:$AB$13,,MATCH(L$4,$S$4:$AB$4,0)),0),MATCH(L$4,$S$4:$AB$4,0)+1)

Select allOpen in new window

and then copy across and down.
match-and-lookup.xlsx

i think you need Match and Index functions.

use formula such as:

=INDEX($T$5:$T$13, MATCH(B5,$S$5:$S$13,0))

Select allOpen in new window

match-and-lookup_b.xlsx

Hi Chaps,
Could you try it on the spreadsheet I provided as neither produced the required result
Thanks

Did you check the file I attached in my post? I have implemented the formula in there and applied a border to all the formula cells.

Neeraj,
I have checked your spreadsheet and corrected top line 'prunes' 'bananas'

I got a #N/A error in the last column 'bananas'

Because there is a trailing space in I4, remove it and it will resolve your issue. I did the same thing in the file I attached. :)

OH yes that was a strange one. They are looking for another Poirot for the next series. Interested ?

Thanks guys for your suggestions.

You're welcome Ian! Glad it worked as desired in the end. :)