VLOOKUP Returning #NA

Hello,

I have used VLOOKUP a fair amount but I am stumped why I keep on getting #NA.

I have checked if additional text is in a string -- hence I used the TRIM function to clean it

Does anyone have any ideas? I have attached the data for your reference. I am looking to return the state in column G (CMS Bundled Payment Data Tab) based on the city being present in both tables.

Any help, much appreciated.

All the best,
Adam

Quite a few problems with your formula there, Adam.

Firstly, the lookup reference is off by 1 row (formula in row 2 is trying to find the header in A1)
Secondly, the table reference is not anchored, thus resulting the the reference to that table moving along as you copy it down.
Third, the column number value of 4 is incorrect as the table only contains 2 columns worth of data (I suspect its from you deleting some columns to preserve confidentiality)
Lastly, the table should be structured differently. The first column of the table should be the cities (as you are trying to find them) and the 2nd column should be the states.

I've swapped the columns in US States & Cities around and changed the formula in G2 to

Formula:

Please Login or Register  to view this content.

It should work now. See attached.

Hi Adam - try using this instead....paste in cell G2 of "CMS Bundled)"

=INDEX('US States & Cities'!$A$2:$A$29471,MATCH('CMS Bundled Payment Data'!A2,'US States & Cities'!$B$2:$B$29471,0))

Hi,

Thank you so much! Yes, I deleted some columns to preserve confidentiality.

Thanks for pointing out my mistake!

All the best,
Adam

Thanks to all for all your feedback.

Much appreciated!!

- Adam