 ## 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:

## 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:

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

More To Explore