Speeding UP Index/Match

[greghealey]

I recently changed my spreadsheet from vlookup to Index/Match. I have approximately 2000 rows by 300 columns which all require in vary degrees use of the index/match formula, for example:

=IFERROR(INDEX(Orders!$S$1:$S$9999,MATCH(CT$3&$B4&$C4,Orders!$A$1:$A$9999,0)),0)

Currently the spreadsheet can take up to around 9 minutes to run.

I am looking for ways to improve speed in the spreadsheet, one potential way could be to bring the "Orders" tab into the current tab, I am not sure if this will materially help speed but I am open to any solutions.

Spreadsheet data must be exact match and it cannot be ordered.

Many thanks

 

[ImranMalek]

Hi Greg,

Simple solution would be to add a helper column say $CZ4=CT$3&$B4&$C4 copy it down and change your formula to

=IFERROR(INDEX(Orders!$S$1:$S$9999,MATCH([B]$CZ4[/B],Orders!$A$1:$A$9999,0)),0)

It should work lot faster than the original formula but if you want it to be more faster than continue reading.


If you provide 0 as last parameter to Match function it will perform an exact search
i.e. it will start searching with first cell in your case A1 and let's say if your value is found at position 10,000 it will have to do 10,000 searches.

On contrary if your range is sorted (assumign decending order) excel does a binary search what it means is excel will divide your data into half and directly check cell A5000 if it's value is larger than your search it will now take the other half (5000-10000) and split it into half and it will check cell A7500 if its value is still larger it will continue the search pattern untill the value is found.

Assuming your value is still at position 10,000 it will roughly take ~13 searches rather than 10,000 like in the first case.

If you can sort your range and perform search on column A with ommiting 0 as last parameter, it would perform much much faster.

Hope it helps !