How to optimize vlookup for high search count ? (alternatives to VLOOKUP)
By : NOEL
Date : March 29 2020, 07:55 AM
it should still fix some issue I considered the following alternatives: VLOOKUP arrayformula MATCH / INDEX VBA (using a dictionary) code :
A B
1
2 key51359 {=VLOOKUP(A2:A10001;sheet1!$A$2:$B$100001;2;FALSE)}
3 key41232 formula in B2
4 key10102 ... extends to
... ... ...
99999 key4153 ... cell B100001
100000 key12818 ... (select whole range, and press
100001 key35032 ... CTRL+SHIFT+ENTER to make it an array formula)
100002
A B C
1
2 key51359 =MATCH(A2;sheet1!$A$2:$A$100001;) =INDEX(sheet1!$B$2:$B$100001;B2)
3 key41232 =MATCH(A3;sheet1!$A$2:$A$100001;) =INDEX(sheet1!$B$2:$B$100001;B3)
4 key10102 =MATCH(A4;sheet1!$A$2:$A$100001;) =INDEX(sheet1!$B$2:$B$100001;B4)
... ... ... ...
99999 key4153 =MATCH(A99999;sheet1!$A$2:$A$100001;) =INDEX(sheet1!$B$2:$B$100001;B99999)
100000 key12818 =MATCH(A100000;sheet1!$A$2:$A$100001;) =INDEX(sheet1!$B$2:$B$100001;B100000)
100001 key35032 =MATCH(A100001;sheet1!$A$2:$A$100001;) =INDEX(sheet1!$B$2:$B$100001;B100001)
100002
A B
1
2 key51359 {=vbalookup(A2:A50001;sheet1!$A$2:$B$100001;2)}
3 key41232 formula in B2
4 key10102 ... extends to
... ... ...
50000 key91021 ...
50001 key42 ... cell B50001
50002 key21873 {=vbalookup(A50002:A100001;sheet1!$A$2:$B$100001;2)}
50003 key31415 formula in B50001 extends to
... ... ...
99999 key4153 ... cell B100001
100000 key12818 ... (select whole range, and press
100001 key35032 ... CTRL+SHIFT+ENTER to make it an array formula)
100002
Function vbalookup(lookupRange As Range, refRange As Range, dataCol As Long) As Variant
Dim dict As New Scripting.Dictionary
Dim myRow As Range
Dim I As Long, J As Long
Dim vResults() As Variant
' 1. Build a dictionnary
For Each myRow In refRange.Columns(1).Cells
' Append A : B to dictionnary
dict.Add myRow.Value, myRow.Offset(0, dataCol  1).Value
Next myRow
' 2. Use it over all lookup data
ReDim vResults(1 To lookupRange.Rows.Count, 1 To lookupRange.Columns.Count) As Variant
For I = 1 To lookupRange.Rows.Count
For J = 1 To lookupRange.Columns.Count
If dict.Exists(lookupRange.Cells(I, J).Value) Then
vResults(I, J) = dict(lookupRange.Cells(I, J).Value)
End If
Next J
Next I
vbalookup = vResults
End Function

VlookUp working with Numbers but Not Text
By : Jb Rifluxyss
Date : March 29 2020, 07:55 AM
hope this fix your issue I have been using VLookUp for matching numbers I want trying to use it on text with exact match , Change lValue to a variant code :
Dim lValue As variant

Vlookup Not working on text between two tables
By : Naveen
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , @Jeeped's comment has a good answer: Assuming you have already trimmed off leading and trailing spaces, one of the John Smith entries (likely the one from the web) uses a nonbreaking space (e.e. CHAR(160) or ASCII 0×A0) instead of a regular space (e.g CHAR(32) or ASCII 0×20). Use code :
=CODE(MID(A$1, ROW(1:1), 1))

How to delete found values from Vlookup function but not from excel itself, so the vlookup wont search the cells again
By : KMi
Date : March 29 2020, 07:55 AM
may help you . Unless you need the values to dynamically update; the VBA is a better alternative to 100K formulas. Using an ArrayList and arrays it took 1.98 seconds to process the data. code :
Sub ValidateData()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'You'll need to adjust these const values
Const LOOKUP_SHEET As String = "newsheet"
Const TARGET_SHEET As String = "oldsheet"
Dim x As Long, y As Long
Dim data As Variant, results As Variant
Dim key As String
Dim list As Object
Set list = CreateObject("System.Collections.ArrayList")
With Worksheets(LOOKUP_SHEET)
'Load the values from columns A and B into an array
data = .Range("A1", .Range("A" & .Rows.Count).End(xlUp)).Resize(, 2)
End With
For x = 1 To UBound(data, 1)
'Create a unique identifier
'using a delimiter to ensure value don't mix
key = data(x, 1) & "" & data(x, 2)
If Not list.Contains(key) Then list.Add key
Next
With Worksheets(TARGET_SHEET)
'Load the values from columns A and B into an array
data = .Range("A1", .Range("A" & .Rows.Count).End(xlUp)).Resize(, 2)
'Resize the results array
ReDim results(1 To UBound(data), 1 To 1)
For x = 1 To UBound(data, 1)
'Create a unique identifier
'using a delimiter to ensure value don't mix
key = data(x, 1) & "" & data(x, 2)
results(x, 1) = IIf(list.Contains(key), "Correct", "Wrong")
Next
.Range("C1").Resize(UBound(results, 1)) = results
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

How do combine formulas to be used for VLOOKUP to search for a part number in a text string?
By : user7104101
Date : March 29 2020, 07:55 AM
I wish did fix the issue. I have a list of part numbers that are combined with their old part numbers so e.g. PN1234/XMS1234, the XMS part number is what I need and the strings are all different lengths and in different positions. So I need VLOOKUP to find the XMS part number and bring me the price from a different spreadsheet that has the part number correctly. , What about this: code :
=IF(LEFT(A1,3)="XMS",LEFT(A1,SEARCH("/",A1)1),RIGHT(A1,LEN(A1)SEARCH("XMS",A1)+1))

