Vlookup very slow

[leeksleeks]

Hi,

I have a large excel file of golf results (150,000 rows) and I have a vlookup formula that will look though the results and tell me what position the golfer came in previous years when playing on that course. However the vlookup formula I use takes forever to calculate and seem to be calculating any chance it gets (calculating 4 processors). I have read on other posts that people with many more rows of information have found very quick ways of calculating their results, so I was wondering if anyone can help me out.

I have two main worksheets. 1 called Results and 1 called Summary.


On the worksheet called RESULTS I enter the results from each tournament. Below is how this looks starting from Cell A1. The concatenation I use is for the lookup on the next worksheet called SUMMARY. Below is the information I add to the RESULTS worksheet:

[TABLE="width: 500"]
<tbody>[TR]
[TD]=CONCATENATE(C2&J2&K2)[/TD]
[TD]POS[/TD]
[TD]PLAYER[/TD]
[TD]TO PAR[/TD]
[TD]R1[/TD]
[TD]R2[/TD]
[TD]R3[/TD]
[TD]R4[/TD]
[TD]TOT[/TD]
[TD]COURSE[/TD]
[TD]YEAR[/TD]
[TD]TOURNAMENT[/TD]
[TD]TOUR[/TD]
[/TR]
[TR]
[TD]GolferAPebbleBeach2013[/TD]
[TD]1[/TD]
[TD]Golfer A[/TD]
[TD]-12[/TD]
[TD]69[/TD]
[TD]69[/TD]
[TD]69[/TD]
[TD]69[/TD]
[TD]276[/TD]
[TD]Pebble Beach[/TD]
[TD]2013[/TD]
[TD]US Open[/TD]
[TD]PGA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The below table is what the Summary worksheet looks like. I have hidden a few columns but the column cell references are correct. The information I am wanting from this worksheet is The Golfer (A2), The Course (A1) and The Year (M1). Below is an example of what the SUMMARY worksheet looks like:


.....A.......M......N..................AB....................................AC
[TABLE="width: 500"]
<tbody>[TR]
[TD]Pebble Beach[/TD]
[TD]2012[/TD]
[TD]2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Golfer A[/TD]
[TD]3[/TD]
[TD]70[/TD]
[TD]=CONCATENATE(A2,A1,M1[/TD]
[TD]=CONCATENATE(A2,A1,N1)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Golfer B[/TD]
[TD]5[/TD]
[TD]-[/TD]
[TD]=CONCATENATE(A3,A1,M1[/TD]
[TD]=CONCATENATE(A3,A1,N1)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Golfer C[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]=CONCATENATE(A4,A1,M1[/TD]
[TD]=CONCATENATE(A3,A1,N1)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Golfer D[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]=CONCATENATE(A5,A1,M1[/TD]
[TD]=CONCATENATE(A3,A1,N1)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So in order for me to find out how golfer A did in 2012 at Pebble Beach I am looking up the concatenation from both worksheets using the following code: (The results of 3 would appear in cell M2 above.

=IF(ISNA(VLOOKUP(AB2,Results!$A:$B,2,FALSE)),"-",VLOOKUP(AB2,Results!$A:$B,2,FALSE))

However when I am trying to lookup 500 golfers at once it takes an absolute age to produce their results. Is there a better way I could do this so that it speeds up the lookup?

I look forward to hearing if anyone can speed up this process.

 

[Asala42]

I'd use something like this:

=IFERROR(VLOOKUP(AB2,Results!$A$1:$B$150000,2,0),"-")

points:
1) The iferror will cut down the number of lookups
2) The distinct range means you are not doing lookups in over 1million rows

Sorting the table on Results column A could also help, if you're free to do so.

 

[leeksleeks]

Thank you for the reply. This appears to have sped things up and it doesn't do as many 'calculating processors. It does however still take 6minutes to open the file and 35 seconds to change one fo the criteria but I think that may be down to my computer more than anything. Thanks for all your help!

 

[ameydabholkar]

change the format in which you have saved your workbook to .xlsb binary Workbook

Regards
Amey