Keep in touch and stay productive with Teams and Office 365, even when you're working remotely. VLOOKUP will only look for a closest match to a value (by default) or an exact value. #NAME & REF! Now we will see what happens if we pass table B2:C7 by mistake, instead of B2:D7. Similarly to the first example, we have VLOOKUP formula in the cell G3. Learn more It does not seem to matter if I format both columns as text or numbers, same issue (the full data set column does have values that are numbers only & alpha-numeric in both the search criteria and the target range). Here we discuss how to fix the 4 common errors #N/A, #VALUE! You Forgot to Reference Lock Your Arrays. Guide to VLOOKUP Errors in Excel. When I apply the VLookup to both fields, I get the value of 409 for BOTH, so the 'exact value' part of the formuala does not seem to be working. 1. As the Sales is $500, we got that value in the cell G3. VLOOKUP is very common, popular and widely used function in Excel and Google Sheets. But the majority of users complain that VLOOKUP is not working correctly or giving incorrect results. =vlookup( a2, sheet1!#ref!, 2, false ) Quick Fix to this Problem: Use Absolute References The simplest way to prevent a reference to a range from changing when copied to other cells is to use absolute references within the formula. This is because of some limitations with the VLOOKUP function, and sometimes users also do not carefully follow its rules and syntax. You Have Your Numbers Formatted as Text 2. 275 comments. The purpose is to get the Sales value for Product B from the table in the range B2:D7. I have both lists sorted A-Z. in Excel. in VLOOKUP with excel example. The working example of the VLOOKUP function. You Have a Trailing Space at the End of Your Values 3. VLOOKUP also assumes by default that the first column in the table array is sorted alphabetically, and suppose your table is not set up that way, VLOOKUP will return the first closest match in the table, which may not be the data you are looking for.