Vlookup not working in large file

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.