Index Match Returns #NA if Reference

[perrymans]

Does work (regardless of C4 contents):
=INDEX(Details!$J$2:$J$50000,MATCH('Vulnerator Translator'!C4,Details!$P$2:$P$5000,0))

Does not work (if C4 is a formula):
=INDEX(Retina!$C$3:$C$10,MATCH('Vulnerator Translator'!C4,Retina!$A$3:$A$10,0))

However this second one will work if the content of C4 is changed from a lookup to an actual typed number...
i.e. typing 12370 vs =TRIM(RIGHT('POA&M'!K2,LEN('POA&M'!K2)-FIND("Audit ID:",'POA&M'!K2)-9))

I am using that C4 as a reference for many things, all of which work when pointing to Details or POA&M. But if I point to Retina worksheet, it fails.

Thanks. Sean.

 

[barry houdini]

Hello Sean,

When you use RIGHT function (and others) the result is a text value (although it might look like a number), try adding zero to get a number

=TRIM(RIGHT('POA&M'!K2,LEN('POA&M'!K2)-FIND("Audit ID:",'POA&M'!K2)-9))+0

or this shorter version should work

=REPLACE('POA&M'!K2,1,FIND("Audit ID:",'POA&M'!K2)+8,"")+0