Vlookup gives wrong answers when used in large data. Pls advise?

[Dave]

I've been using vlookups for a couple of years now and my colleague and I
have noticed that when we use them in large speadsheets (50k lines+) the
vlookup comes out with the wrong answer. As anyone else ever come across
this? It happens especially when I use VBA to calculate vlookups for
particular cells.

Any advice would be very much appreciated. Thank you.

 

[Ardus Petus]

I hit the same problem with a VLOOKUP on ca. 1000 rows.
Tried to get around with INDEX(MATCH)), results were the same.

HTH

 

[Niek Otten]

This never happens with correct data and formulas, not even with 64K lines tables.

Frequent causes:

1. Some cells look like numbers, but are actually text. You can check with the ISTEXT function. Check both the search arguments
and the table. Formatting as numbers afterwards doesn't help.
Remedy:
Format an empty cell as Number. Enter the number 1. Edit>Copy. Select your "numbers". Edit>Paste Special, check Multiply.

2. The data is not sorted and the 4 argument of the VLOOKUP is TRUE or omitted.

3. There are spaces or other invisible characters in either the search arguments or the table.

How exactly do you use VBA to calculate vlookups? What are the values of the arguments?

 

[Niek Otten]

<This never happens > Of course I should have said "this never happened to me"

 

[Pete_UK]

Another common problem is forgetting to use absolute references for the
lookup table range.

Hope this helps.

Pete