Excel VLOOKUP vs INDEX and MATCH Speed Comparison
In this article I will be comparing the calculation speed of the function VLOOKUP with that of the INDEX and MATCH combination. For more information about the VLOOKUP function please see the link below:
For more information about the MATCH INDEX combination please see the link below:
Contents
Data:
The data used in both tests was the same. 3 columns of data.
Column 1: Key values, starting from “1” going up to “20000”.
Column 2: Dummy column
Column 3: Values to return. Starting from “Value 1” going up to “Value 20000”
In both tests column A was searched for the value “20000” and the value in column 3 was returned.
Test 1, VLOOKUP:
In the first test VLOOKUP was tested. The range “F1:F20000” was filled with the function below:
=VLOOKUP(20000, A$1:C$20000, 3, FALSE)
Thats 20,000 VLOOKUP functions. Then the following VBA code was run. The code causes the sheet to recalculate its functions 1000 times. Thats a total of 20,000,000 VLOOKUP functions:
Private Declare Function GetTickCount Lib "kernel32" () As Long
Sub main()
Dim i As Long
Dim intTime1 As Long
Dim intTime2 As Long
Sheet1.EnableCalculation = False
intTime1 = GetTickCount
For i = 1 To 1000
Sheet1.Calculate
Next i
intTime2 = GetTickCount
MsgBox (intTime2 - intTime1)
End Sub
Completion Time: 0.812 Seconds
Test 2, INDEX and MATCH:
In the second test the INDEX and MATCH functions were tested. The range “F1:F20000” was filled with the function below:
=INDEX(C$1:C$20000, MATCH(20000, A$1:A$20000, 0))
The same VBA code was run:
Sub main()
Dim i As Long
Dim intTime1 As Long
Dim intTime2 As Long
Sheet1.EnableCalculation = False
intTime1 = GetTickCount
For i = 1 To 1000
Sheet1.Calculate
Next i
intTime2 = GetTickCount
MsgBox (intTime2 - intTime1)
End Sub
Completion Time: 0.842 Seconds
Results:
Method | Completion Time | Calculation Count |
VLOOKUP | 0.812s | 20E6 |
INDEX, MATCH | 0.842s | 20E6 |
The VLOOKUP function is slightly faster than the INDEX, MATCH combination
You can download the file and code related to this test from the link below:
See also:
- Excel VBA, Sheet Reference Speed
- Excel Speed, IF (Formula vs VBA)
- Excel INDEX MATCH Functions
- Excel, MATCH() function
- Excel INDEX() Function
- How to use the Match function via VBA
If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com