Vlookup - More than 5 mil data

[Niranjanrajrishi]

I searched many forums to find a better coding to apply Vlookup in multiple columns. My file has 2 sheets and the Sheet1 requires values from the Sheet2. I tried multiple coding but the huge data turned everything down.

Can someone help me with a better coding which works with huge data?

Set Rng = Range(Cells(2, "AH"), Cells(Rws, "AH")).SpecialCells(xlCellTypeVisible)
      If Not Rng Is Nothing Then Rng.Value = "=IFERROR(VLOOKUP(AG:AG,'Previous Week Data'!AF:AG,2,0),""Need to verify"")"
    End If

 

[Marc L]

As SpecialCells is a mess with big data, use a filter or an advanced filter.

Often huge data means Access instead of Excel …​

 

[Chihiro]

If you have access to PowerQuery, that's your best option within Excel for large data set. Otherwise, I'd consider using Access, MS SQL, MySQL or other database. Depending on what's available.

Other than that, consider splitting file into manageable size.

 

[Debaser]

If you can sort the data on the Previous week sheet using column AF, you can use the binary search version of VLOOKUP which is much faster.