sorry slipped when trying to illustrate data. looks like 'true' is the
way to go.


On Mar 3, 5:05 pm, Brotherharry wrote:
My key column is a unique number, so it would need to be an exact
match, but do I understand you correctly in saying that if the vlookup
range is sorted in ascending order, then if the range lookup is
'true', excel would stop as soon as it hits the first instance of the
look up ID,

e.g.

lookup value = 45

data

34
36
45
54
67
45

On Mar 3, 4:56 pm, Luke M wrote:

If your VLOOKUP range is sorted alphabetically by key column, you could use a
'range lookup' of true(or omitted), thus eliminating the need for XL to look
at all the rows everytime. Downside of this is if you need an exact match,
this won't work.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

"Brotherharry" wrote:
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?