VLookup returning #NA - syntax correct

%3CLINGO-SUB%20id%3D%22lingo-sub-2525585%22%20slang%3D%22en-US%22%3EVLookup%20returning%20%23NA%20-%20syntax%20correct%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2525585%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20struggling%20to%20understand%20why%20I'm%20getting%20an%20%23NA%20error.%20The%20formula%20is%20correct%20from%20what%20I%20can%20tell.%20Formula%20is%3A%26nbsp%3B%3DVLOOKUP(Expenses!A2%2CSheet1!%24A%242%3A%24B%2449214%2C2%2CFALSE)%3C%2FP%3E%3CP%3E3%20page%20excel%20workbook%2C%20tab%201%20not%20referenced%2C%20tab%202%20is%20source%20lookup%20(Expenses)%2C%20tab%203%20is%20table%20array%20(Sheet1).%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20am%20I%20missing%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2525585%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2525823%22%20slang%3D%22en-US%22%3ERe%3A%20VLookup%20returning%20%23NA%20-%20syntax%20correct%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2525823%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1098060%22%20target%3D%22_blank%22%3E%40K2good%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3EWhat%20am%20I%20missing%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3ESimple%20answer%3A%20There%20is%20no%20value%20in%20Sheet1!A2%3AA49214%20which%20is%20equal%20to%20Expenses!A2.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.