Can't find the error: VLOOKUP not returning the value

excel formatting syntax-error excel-formula vlookup
user1023021 picture user1023021 · Nov 2, 2012 · Viewed 35k times · Source

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 !

Answer

nutsch picture nutsch · Nov 2, 2012

Using this formula will address the issue highlighted by RocketDonkey

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

Related questions

Excel CSV - Number cell format

I produce a report as an CSV file. When I try to open the file in Excel, it makes an assumption about the data type based on the contents of the cell, and reformats it accordingly. For example, if the …

Read More
(Excel) Conditional Formatting based on Adjacent Cell Value

I'm trying to apply conditional formatting in Excel on a range of cells, based on the adjacent cell's value, to achieve something like this: The goal is to highlight values in Column B (Actual Expense) red if the value is …

Read More
Ordering issue with date values when creating pivot tables

I have a 2-column spreadsheet which contains Dates and Sales figures as follows: When doing Right click -> Format Cells the values in the Dates column properly appear as Date (in the M/D/YYYY format). This applies to …

Read More