Index match match with helper column

I'm attempting to return a value from an INDEX MATCH MATCH formula using a helper column to assign a unique value as a look up value. I keep getting #N/A. Cant figure out what is wrong? Ive tried in a table and not formatted. Ive attached a copy and the formula is in cell G55. Appreciate any help you can offer

Try this:

=INDEX(TBLTOPERFORM[[-20]:[47]],MATCH(D55,TBLTOPERFORM[CONCATENATE],0),
MATCH(E55&"",TBLTOPERFORM[[#Headers],[-20]:[47]],0))

Try

Formula:

Please Login or Register  to view this content.

Thank you very much. both worked. Must have had a problem with the number (e55) value???

Yes, E55 is number , [-20]:[47] is text.

Originally Posted by Phuocam

Yes, E55 is number , [-20]:[47] is text.

As XL automatically aligns text left and numbers right, manual alignment removes that feature, hiding eventual " mistakes" or " differences".
So if you do not need these cosmetic features ( or if your boss doesn't mind) don't use manual alignment