Alternative to match formula for speed

**stephme55** · 08-04-2016, 06:42 PM

Hello. Is there any alternative to a match formula for speed? My spreadsheet dies with this formula.

=MATCH(D776334,'Renewals .csv'!$A$2:$A$463252,0)

**FDibbins** · 08-04-2016, 07:44 PM

Try converting your.csv file to an excel file

**teylyn** · 08-04-2016, 08:34 PM

Hello,

this may sound bizarre, but if you sort your data by column A, you can use two approximate lookups, which are a lot faster than the exact match lookup.

=IF(VLOOKUP(D776334,'Renewals .csv'!$A$2:$A$463252,1,TRUE)=D776334,MATCH(D776334,'Renewals .csv'!$A$2:$A$463252,1))

Charles Williams explains it here: https://fastexcel.wordpress.com/2012...han-1-vlookup/

Let me know if that works for you.

cheers, teylyn

**teylyn** · 08-04-2016, 08:49 PM

Also, check out Mike Alexander's evaluation of the double lookup trick. Quoting from this page

At one extreme, if your lookup table has 10,000 things in it, the double VLOOKUP trick on sorted data is 28 times faster than the standard VLOOKUP on unsorted data
At the other, if your lookup table has 1,000,000 things in it, the double VLOOKUP trick on sorted data is 3,600 times faster than the standard VLOOKUP on unsorted data

Double-VLOOKUPv2.gif