Why does this work? Exploring the VLOOKUP formula

1
votes

I have an excel 2010 spreadsheet with 4 columns.

Column A: A list of UPC codes for products I sell. Around 300 lines.

Column B: Formula (more on this later)

Column C: Another list of UPC codes. These UPC codes are around 10,000 lines.

Column D: An inventory count which corresponds to UPC codes in Column C.

The formula: 

=VLOOKUP(A2,C:D,2,FALSE)

The idea is to match up my UPC codes with my supplier's UPC codes to retrieve the corresponding inventory count.

All data was pasted into a new spreadsheet from other spreadsheets and were pasted as values only to ensure no other characters or formatting was imported.

This formula was dragged down for all 300 lines.

I received the error #N/A in Column B until I inserted the following character before the values in Column A:

'

Note: the ' was inserted manually by clicking on the cell and typing ' in the box above.

Immediately after this was done, the #N/A would change to the corresponding inventory retrieved from Column D.

My question is why does this work? Why does the method without the ' not work? Is there a way to quickly add the ' before all values in Column A? Note: CONCATENATE("'",A2) does not work for some reason.

2
excelexcel-formulaexcel-2010

2 Answers

3
votes

raphael you've got it there - your number is stored as text in one of the lookup tables, and as a number in the other. Adding a ' to each line will fix it, but it's pretty horrible task. Clicking the green arrow can also fix it, but excels default method can be really slow for big tables (And it also changes your source data, which for me is a no-no, as when you update the data, you'll have to do it again). Changing the formatting rarely helps in this situation.

If the table contains numbers and lookup value is number-stored-as-text, you can multiply the lookup value by 1:

=VLOOKUP(A2*1,C:D,2,FALSE)

If the table contains "text" and lookup value is number, you can convert it to text like this:

=VLOOKUP(TEXT(A1,0),C:D,2,FALSE)
0
votes

Here's my theory. In VLOOKUP, if the lookup value (A2) is smaller than the smallest value in the looked-up table (C:D) then it returns N/A. When you prepend the quote sign (') you make A2 a string, not a number. So for example a UPC of 1002 is no longer treated as the number one-thousand-and-two but as the string sequence one-zero-zero-two. That lets VLOOKUP do the comparison differently and avoid the N/A.

You might try Ctrl-1 in column A and get it to treat them all as text.