0

I have the following formula:

=VLOOKUP(VLOOKUP(A1,[Clients]Sales!$B$1:$C$6,2,0),[Ledger]Sheet1!$G$1:$H$6,2,0)

that is working but I have over 100k lines of data and it is taking a few minutes to extract all the results.

Can it be speeded up?
Is VBA the quickest option?

Community
  • 1
  • 1
davidjwest
  • 538
  • 3
  • 22
  • With lookup tables that small, there must be a lot of repeated data in column A. If you can sort that column, you can reduce the VLOOKUPs to only the first instance of a value, and then just repeat that value for each subsequent instance. – Rory Apr 16 '15 at 12:58
  • Set your worksheet to `Manual Updating` so it's not refreshing every time you change a cell. In general, Excel's built in functions are going to be quicker than VBA code you write. – FreeMan Apr 16 '15 at 12:59

2 Answers2

2

It is difficult to completely answer your questions without sample data, but I think this is what you are looking for: How to optimize vlookup for high search count ? (alternatives to VLOOKUP)

If you are not familiar with VBA, I would definitely look into using INDEX-MATCH.

Hope this helps!

Community
  • 1
  • 1
Gohawks
  • 1,044
  • 3
  • 12
  • 26
1

I imagine you'll want to use index-match. It's a pair of functions that can replicate vlookup and more, but is much faster than vlookup. I almost never use vookup for this reason any more.

I think what you want looks like below. No promises without seeing your workbook though.

=index([Ledger]Sheet1!$H$1:$H$6,match(index([Clients]Sales!$C$1:$C$6,match(A1,[Clients]Sales!$B$1:$B$6,0)),[Ledger]Sheet1!$G$1:$G$6,0))

The explanation is index pulls the nth item from a specific column. Match finds what n that happens to be.

Dylan Cross
  • 544
  • 2
  • 6
  • 14