Does structure of data affect speed of vlookup?
 

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?

Does structure of data affect speed of vlookup?
 

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?

Does structure of data affect speed of vlookup?
 

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?

Does structure of data affect speed of vlookup?
 

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?

Does structure of data affect speed of vlookup?
 

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?

Does structure of data affect speed of vlookup?
 

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

Or, probably "better":

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Niek Otten" wrote in message
...
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?

Does structure of data affect speed of vlookup?
 

Bingo!

Awesome.

Adding that one to my 'best spreadsheet in the world ever.xls' list of
useful functions.

Thanks Niek.

On Mar 3, 5:49 pm, "Niek Otten" wrote:
<=IF(C1=VLOOKUP(C1,A1:B4,1),VLOOKUP(C1,A1:B4,2),"" )

Or, probably "better":

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Niek Otten" wrote in message

...

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?