VLookup returning #NA - syntax correct

New Contributor

I am struggling to understand why I'm getting an #NA error. The formula is correct from what I can tell. Formula is: =VLOOKUP(Expenses!A2,Sheet1!$A$2:$B$49214,2,FALSE)

3 page excel workbook, tab 1 not referenced, tab 2 is source lookup (Expenses), tab 3 is table array (Sheet1). 

What am I missing?

 

Resolved: The table array was not sorted in ascending order AND the results column was not formatted the same as the other source column. 

4 Replies

@K2good 

 


What am I missing

Simple answer: There is no value in Sheet1!A2:A49214 which is equal to Expenses!A2.

 

 

@K2good The result #NA means that column A on Sheet1 does not contain an exact match for the value of A2 on Expenses. For example, if Expenses!A2 contains "John " and there is a cell in Sheet1!A2:B49214 that contains "John" but no cell that contains "John ", there is no exact match.

Actually, I checked and the value does exist in the table. The actual problem was the array was not sorted in ascending order AND column B was not formatted the same as the others. Took a minute to get it figured out (with help from an expert).
Actually the value did exist in the table. It was a formatting and sort issue.