Can't find the error: VLOOKUP not returning th

问题:

I am working with an excel file and I am trying to "map" some values with the vLookUp function.

The first parameter of the vLookUp function is giving me headaches: The function works with certains values (typed by hand in a text format)...but doesnt work with pre-generated values (the sames ones...but generated from an ERP system..). I checked to make sure that I was looking for "same nature" values ( text vs text ) ...but I can't find out why the first 3 values (typed by hand) are fine... but the last 3 (pre generated) gives me a #N/A error: It has to be a difference of "format" ..but I can't find out where's the difference...

This is the formula used: =VLOOKUP(D1;$A$1:$B$219;2;FALSE)

The actual file

1008600 379.99      1008600 379.99
1008601 379.99      1008601 379.99
1010600 449.99      1010600 449.99
1010601 449.99      1010601 #N/A
1013600 489.99      1013600 #N/A
1014601 509.99      1014601 #N/A
1015600 569.99          
1018603 679.99          
1019600 809.99          

Thank you !

回答1:

Using this formula will address the issue highlighted by RocketDonkey

=VLOOKUP(TEXT(D1,"#"),$A$1:$B$219,2,FALSE)

回答2:

The issue is that you haven't converted you values in column A to numbers. Since the first three values in your lookup range are themselves strings, they are matching the stringified numbers.

In order to fix, click on one of the numbers in column A, press Ctrl+Space to select the entire column, and then click on the exclamation mark and select Convert to number:

After that, you'll notice that your previous values now show errors, so follow the same process to conver them to numbers as well. @nutsch's solution is does not require you to modify your data, so if the type of your data is not crucial, that is a good fix as well :)

回答3:

This has been a major pain for me as well(Excel is full of stupid assumptions). What I do is copy the entire column to a text editor, change the column to match the lookup column, and copy and paste the data back into the column. This has worked well for me.

回答4:

May I highlight the little noticeable reply that Barry Houdini came with in 2012 above:

    =VLOOKUP(D1&"",$A$1:$B$219,2,FALSE)

that really works (for me in Excel 2010), despite you are dealing with Numbers or a mix of Numbers or Text strings.

Cudos to Barry, you are a truely Houdini