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:

• VLOOKUP, Excel Functions

For more information about the MATCH INDEX combination please see the link below:

• Excel INDEX MATCH Functions

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:

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:

• VLOOKUP and INDEX MATCH Speed Test Excel.xlsm

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