Vlookup not working in large file

[kgilberg]

I am doing a vlookup comparing two large files (200,000+ rows). The data I'm doing to vlookup on are in the number format. The vlookup will return approximately 2/3 of the data, but the other 1/3 shows #N/A. When I spot check the number in the comparison files, it shows that data should have appeared, but is not. Any tips or tricks for things to test?

[#2]

@kgilberg 

Make sure that the lookup range covers all used rows.

Do you use FALSE as 4th argument of VLOOKUP?

[#3]

I have verified the look up range and yes, I use FALSE because I'm looking up SKUs that could be similar.

[#4]

@kgilberg  wrote:  ``Any tips or tricks for things to test?``

You don't give us much to work with -- not even the formula(!).

First, the format of the cells do not matter.  What matters is the type of the lookup value (first parameter) and type of the values in the lookup column.

Looks can be deceiving.  Use formulas of the form =ISNUMBER(A1) to confirm that all data are truly numeric.

-----

Second, are you using VLOOKUP(..., FALSE) for an exact match or VLOOKUP(..., TRUE) for an approximate match?

(PS.... You answered that question in a response posted while I was writing.)

For VLOOKUP(..., TRUE), are the numerical values in ascending order?

For VLOOKUP(..., FALSE), are the internal binary values exactly equal?

A simple test like X1=Y1 is not sufficient because Excel rounds internally to 15 significant digits for comparison operators.

So, that might result in TRUE, but in fact, MATCH(X1, Y1, 0) might result in #N/A, which indicates that the binary values in fact do not match.

You might need to round all values to the precision that you expect to be accurate.  Again, looks can be deceiving because Excel formats only up to 15 significant digits.

For example, 12.48 - 11.25 appears to be 1.23, even when formatted with 14 decimal places.  But MATCH(12.48 - 11.25, {1.23}, 0) returns #N/A, indicating that the binary values are not the same.

[#5]

@kgilberg  wrote:  ``I'm looking up SKUs``

Technically, SKUs should be stored as text, not numbers.  Even if they have numeric characters, they might be longer than 15 characters.

My guess:  some SKUs are stored as text in one set cells, but stored as numbers in another set.  VLOOKUP will not match them.

Again, the format of the cell does not matter.  The type of the cell value does.  Use ISNUMBER(A1) to confirm the type of the value.

[#6]

It's just a basic vlookup, nothing special or advanced about it. I do vlookups often and I'm stumped as to why this will only work on 2/3 of the data. I run this report monthly so all SKUs are the same as last month, we are just adding one more period of data.

Yes, they are exactly equal. I have no decimal points in my data, the data is SKUs not mathematical type numbers, so decimals are not applicable.

=VLOOKUP(F2,'[May 2022 Export - diagnostics.xlsx]May 2022 Export'!$F$2:$R$211707,13,FALSE)

[#7]

@JoeUser2004 I could not use text as it converted my data to numbers with exponents "8E11". 

[#8]

@JoeUser2004 I did this, but what is it really telling me to address my problem? My values match. 

[#9]

When I have this issue, it's usually some data on my lookup table is saved as numer and some as text.
The way I eliminate that as an issue, I select the column(s) in my lookup table, go to Data, select text to Columns, confirm "deliminted" is selected, and click finish. That should convert any text to numbers.