Forum Discussion
kgilberg
Jun 27, 2022Copper Contributor
Vlookup not working in large file
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 sho...
HansVogelaar
Jun 27, 2022MVP
Make sure that the lookup range covers all used rows.
Do you use FALSE as 4th argument of VLOOKUP?
kgilberg
Jun 27, 2022Copper Contributor
I have verified the look up range and yes, I use FALSE because I'm looking up SKUs that could be similar.
- JoeUser2004Jun 27, 2022Bronze Contributor
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.
- kgilbergJun 29, 2022Copper Contributor
JoeUser2004 I did this, but what is it really telling me to address my problem? My values match.
- kgilbergJun 27, 2022Copper Contributor
JoeUser2004 I could not use text as it converted my data to numbers with exponents "8E11".