VBA Vlookup for large data

[sguna1982]

Hi Team,

Need favour about excel VBA for Vlookup function, Since its huge data size, its taking so much of time. So i am looking for excel VBA queries for run vlookup formula. I have 2 excel work books in the data size of around 300000 rows for both files, i am lookuping data from one excel to another excel using vlookup formula, while run the formula its taking more then hour, then paste as values will take again on hour. So need your help to fix this, thanks in advance.

Lookup_value = [Book1.xlsx]Sheet1!A2
Table_array = [Book2.xlsx]Sheet1!$A$2:$B$16
Col_index = 2
Range_lookup = FALSE

After execute this formula, i paste the values from formulas.

Thanks,
Guna

 

[Marc L]

Hi !​

​

A must read :​

Excel formula: Faster VLOOKUP with 2 VLOOKUPS | Exceljet

With large sets of data, exact match VLOOKUP can be painfully slow, but you can make VLOOKUP lightening fast by using two VLOOKUPS, as explained below. Notes: If you have a smaller set of data, this approach is overkill.

exceljet.net

​

 

[sguna1982]

Hi Marc,

Thanks for your response. I am using this suggestion, but the problems are , 1). I need to sort the entire workbook and 2). Again copy and paste the formulas into values is taking time again. Please suggest.

Thanks
Guna

 

[Marc L]

No matters as you can fill the formula by code …​

​

It's a common issue when using Excel for a huge database as it's just a spreadsheet.​

As any database software is at least 50 times faster !!​

​

Other ways you can see in VBA inner help or in blog / threads from this forum :​

• Range.Find
• MATCH worksheet function with memory array
• dictionary
• Using SQL to consolidate two sheets
• …

 

[sguna1982]

Hi Marc,

Thanks for your prompt help.

Since, its huge database and i am not familiar with this. I request VBA macro codes for this action.

I got codes from form for vlookup the values between sheets, but i am looking for workbooks, is it possible to help.

Codes for your reference.

Sub DemoA()
    Application.ScreenUpdating = False
    R& = Sheet1.UsedRange.Rows.Count - 1
For C& = 2 To Worksheets.Count
    With Sheet1.Cells(2, C).Resize(R)
        .Formula = "=VLOOKUP($A2," & Worksheets(C).Cells(1).CurrentRegion.Columns(13).Address(, , , True) & ",1,FALSE)"
        .Formula = .Value
    End With
Next
    Application.ScreenUpdating = True
End Sub

Thanks,
Guna.