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.

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

HTH
--
AP

"Dave" a écrit dans le message de
...
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.

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. EditCopy. Select your "numbers". EditPaste 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?

--
Kind regards,

Niek Otten



"Dave" wrote in message ...
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.

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

--
Kind regards,

Niek Otten

"Niek Otten" wrote in message ...
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. EditCopy. Select your "numbers". EditPaste 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?

--
Kind regards,

Niek Otten



"Dave" wrote in message ...
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.

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

Hope this helps.

Pete