VLOOKUP Calculation Efficiency

[MrKowz]

Friends,

I have a conceptual question regarding VLOOKUPs. I already know it is poor practice to associate a larger table_array with a VLOOKUP than is necessary (i.e. don't refer to A:ZZ if you are returning column D, just refer to A:D), but I'm wondering how much does this affect overall calculation efficiency?

Reason I'm asking is that I'm updating a spreadsheet someone else programmed, and their VLOOKUP formulas are all looking at much larger arrays (A:ZZ instead of A:D, columns past D have no data)... but I don't want to take the time to update these if the performance improvement is going to be miniscule. For simplicity sake, lets assume there are ~7000 of these formulas in the spreadsheet.

Cheers!

 

[inactiveuserps07]

@MrKowz, I don't have an answer to your question precisely; but if E:ZZ have no data, try this:

1. Select Columns E:ZY and delete them (thereby moving the old ZZ to Column E as far as Excel "sees" it).

2. Select Column D, then copy and paste the entire column's data to Column E.

3. Delete Column D

It seems that would quickly update every formula referencing A:ZZ so that it would only reference A:D.

 

[FormR]

Hi, I don't think it has any effect on the calculation speed of the function, BUT, using a larger than required range effectively introduces un-necessary volatility. i.e. those formula will needlessly re-calculate when any cells in the extended ranges are changed.

 

[Aladin Akyurek]

I tend to think the same way as FormR.

If the efficiency is an issue, try to sort the data in ascending order on the match-range (and keep it that way, possibly via a VBA routine). Then invoke as an example...

=IF(VLOOKUP(LookupValue,A:A,1,1)=LookupValue,VLOOKUP(LookupValue,A:Z,4,1),"na")

 

[MrKowz]

Thanks all - good to know some of those inner workings of the VLOOKUP and how the added range introduces volatility! Also, thank you Aladin for the more efficient VLOOKUP - interesting approach. Do you have a reference or article handy which would explain that method in depth (the why's)? Would it be any more efficient than doing an INDEX/MATCH?

Cheers

 

[NARAYANK991]

Hi ,

I think this might help :

https://fastexcel.wordpress.com/2012/03/29/vlookup-tricks-why-2-vlookups-are-better-than-1-vlookup/

 

[NARAYANK991]

Hi ,

Yet another one :

http://www.exceluser.com/blog/727/excels-fastest-lookup-methods-the-tested-results.html

 

[SpillerBD]

Not mentioned yet for formula reference and ease, is using Tables or Named Ranges instead of basic range referencing.
Tables are much my preference and have taking over my thought process over the past few years. CTRL+T !

 

[Aladin Akyurek]

Thanks all - good to know some of those inner workings of the VLOOKUP and how the added range introduces volatility! Also, thank you Aladin for the more efficient VLOOKUP - interesting approach. Do you have a reference or article handy which would explain that method in depth (the why's)? Would it be any more efficient than doing an INDEX/MATCH?

Cheers

MATCH with match-type 1 (or TRUE), VLOOKUP with match-type 1, HLOOKUP with match-type like LOOKUP are all fast because they all recruit a form of binary-search algorithm. See https://en.wikipedia.org/wiki/Binary_search_algorithm for example. The algorithm requires a sorted array (range). The speed should be comaparable:

=IF(LOOKUP(lookupvalue,A:A)=lookupvalue,LOOKUP(lookupvalue,A:A,D:D),"")

=IF(VLOOKUP(lookupvalue,A:A,1,1)=lookupvalue,VLOOKUP(lookupvalue,A:D,4,1),"")

=IF(INDEX(A:A,MATCH(lookupvalue,A:A,1))=lookupvalue,INDEX(D:D,MATCH(lookupvalue,A:A,1)),"")

are all equivalent and should be comparable in speed. (I often suggests the first one, because it's obvious/known that it requires a match-range that is sorted in ascending order.

If sorting is not possible, use a 2-step approach like below:

In say E2...

=MATCH(lookupvalue,A:A,0)

then in F2...

=IF(ISNUMBER(E2),INDEX(D:D,E2),"")

The F2 is thus the result cell.

 

[Aladin Akyurek]

Hi ,

I think this might help :

https://fastexcel.wordpress.com/2012/03/29/vlookup-tricks-why-2-vlookups-are-better-than-1-vlookup/

Hi ,

Yet another one :

http://www.exceluser.com/blog/727/excels-fastest-lookup-methods-the-tested-results.html

Both describe the same idea as the post #4 ...