It helps if the data is sorted (ascending). Then you can set the 4th
argument to TRUE (or omit it), which enables Excel to use fast search
mechanisms (like binary search).
You'd have to check for the presence of your key yourself. that means two
VLOOKUPS (in one formula), but it can still be hunreds to thousands times
faster.
Suppose your table is in A1:B4 and the search argument is in C1:

=IF(C1=VLOOKUP(C1,A1:B4,1),VLOOKUP(C1,A1:B4,2),"")

Really, hundreds to thousands times faster!

If this is not your case, post again in this thread with more details

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Brotherharry" wrote in message
...
I'm having to work with very large data sets using vlookups.

e.g. I've 200k rows with an id in one sheet and I'm looking up
corresponding values in a data range that's 20k rows and 4 columns in
size in another sheet.

I recognise there are probably solutions in moving data off into
Access or whatnot, but hey, I'm here and I like Excel.

so my question is
can I influence the speed of calculation by how my data is structured
in either the source or vlookup range?