Alternative to Vlookup with hundred thousand lines

[MYORCHID26]

Hi,
I have been using vlookup for the longest time, however it takes up so much memory as I use multiple sheets at one time.
Is there an alternative that is faster and effective? I tried index and match and it doesnt look like it works. I get #n/a
In my contacts excel file, I created a vlookup to column A (lookup contact IDs) to acct.xlsx sheet 1 to return the value (name of the contact) in column C.
Issue is that my contacts file has over 300,000 lines.

=VLOOKUP($A:$A,'[Acct.xlsx]Sheet1'!$A:$C,3,FALSE)

 

[James006]

Hi,

Have you tried the Index-Match combination ...?

HTH

 

[Teeroy]

If Vlookup works then a properly formed Index - Match will work, but I doubt it would be any quicker. I can only think of two things that may be quicker; loading an Array or Scripting Dictionary with the data to memory via VBA (but it will reduce available memory) or splitting the contacts over several sheets (or named ranges) so that a smaller range is searched.

 

[clabulis]

Don't use entire columns (such as A:A) when using VLOOKUP. This makes calculations much slower. If, for example, your data goes from A2 to A97659 in a column, then make sure to use those exact numbers.

 

[Armando Montes]

Maybe:

[URL="http://www.ozgrid.com/forum/showthread.php?t=155942"][COLOR=#1155cc]http://www.ozgrid.com/forum/<wbr>[/COLOR]showthread.php?t=155942[/URL]
[COLOR=#0000ff]Sub[/COLOR] test()  
    [COLOR=#0000ff]Dim[/COLOR] a, i [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]Long[/COLOR] 
    a =  Sheets("sheet2").Range("a1").<wbr>CurrentRegion.Resize(, 2).Value  
    [COLOR=#0000ff]With[/COLOR] CreateObject("Scripting.<wbr>Dictionary")  
        [COLOR=#0000ff]For[/COLOR] i = 2 [COLOR=#0000ff]To[/COLOR] [COLOR=#0000ff]UBound[/COLOR](a, 1)  
             .Item(a(i, 1)) = a(i, 2) 
        [COLOR=#0000ff]Next[/COLOR] 
        a =  Sheets("sheet1").Range("a1").<wbr>CurrentRegion.Resize(, 3).Value  
        a(1, 3) = "Supplier Name"  
        [COLOR=#0000ff]For[/COLOR] i = 2 [COLOR=#0000ff]To[/COLOR] [COLOR=#0000ff]UBound[/COLOR](a, 1)  
            [COLOR=#0000ff]If[/COLOR] .exists(a(i, 2)) [COLOR=#0000ff]Then[/COLOR] a(i, 3) = .Item(a(i, 2))  
        [COLOR=#0000ff]Next[/COLOR] 
    [COLOR=#0000ff]End With[/COLOR] 
     Sheets("sheet1").Range("a1").<wbr>CurrentRegion.Resize(, 3).Value = a 
[COLOR=#0000ff]End Sub[/COLOR]

 

[MYORCHID26]

Hi Teeroy and Claudis,
I did try index and match and it's a little quicker and narrowed down by choosing exact # rows. Hope to get a faster processor soon. I will try the VBA that Armando suggested. Though I've never used VBA.

 

[Marcelo Branco]

Maybe this can help

VLookup tricks: why 2 VLOOKUPS are better than 1 VLOOKUP | Excel and UDF Performance Stuff

M.