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.

Excel, Speed Data


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:

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

Leave a Reply

Your email address will not be published. Required fields are marked *