Excel users frequently have a need to lookup data in one spreadsheet and use it in another. To make this job easier, Excel offers built in functions for finding information: INDEX, MATCH, and VLOOKUP. The advantages and disadvantages of these functions are presented below.

INDEX

INDEX returns a value from a range or an array based on its position, or index, in the array. Parameters of the INDEX function are Array and Index.

=INDEX (Array, Index)
=INDEX (Array, row num, col num)

Where Index is a location within the Array.
For an Array with a single row or column, only the row or column number needs to be specified.
For an array with multiple rows and columns, both the row number and column number need to be specified.

The following formula will return the value of the third cell in array A1:Z1:

INDEX(A1:Z1, 3)

If A1:Z1 were filled with the letters of the alphabet, this INDEX would return the letter C, which is the text in the third cell.

MATCH

MATCH provides a way to return the index number of a value stored in an array. It can be helpful to think of Match as a reversed INDEX. In the INDEX function, the Index is specified as a parameter and the formula returns the cell value. In the MATCH function, the cell value is specified as a parameter and the formula returns the Index.
The parameters of the MATCH function are lookup criteria range, criteria range and match type.

=MATCH(Lookup Criteria, Criteria Range, Match Type)

Lookup Criteria is the value that you want to look for in the Criteria Range. The Lookup Criteria can be entered directly into the formula or it can be a reference to a cell that contains the Lookup Criteria.

Criteria Range is a single row or single column of cells where the Lookup Criteria can be found.

Match Type is 0 for an Exact Match, which is the most commonly used match type.

The following formula will determine the index or location of C in array A1:Z1:

= MATCH(“C”, A1:Z1, 0)

The result is 3.

COMBINING INDEX & MATCH

Using INDEX and MATCH together provides for a more robust and versatile lookup function than VLOOKUP, although the formula is a bit longer. Since the MATCH function returns an Index number, it can be inserted within an INDEX function in place of the Index parameter.

=INDEX (Array, Index)

Where Index = MATCH (Lookup Criteria, Criteria Array, Match Type)

=INDEX (Array, MATCH (Lookup Criteria, Criteria Range, Match Type))

The MATCH function looks for the Lookup Criteria in the Criteria Range and then returns its index number. If the Criteria Range is a column, then the row number within the range will be returned by the MATCH function. The INDEX function then returns the value located in the that row of the Array.

Using the below table, we will examine two INDEX/MATCH functions.

In this example, the Array is column B, which contains the city names. The INDEX functions are being used to look up the city name by state in the first function and by name in the second function. In the first function, MATCH is used to find the row containing the state in column C. In the second function, MATCH is used to find the row containing the name in column A.

This feature of looking up a value from within the middle of the table is one of the biggest advantages INDEX/MATCH holds over VLOOKUP. VLOOKUP is limited to looking up values that are located in the leftmost column.

VLOOKUP

VLOOKUP returns values associated with lookup values in the leftmost column of a table range.

=VLOOKUP (Lookup Value, Table Range, Result Column, 0)

To find the state associated with Joe in the table above, the following formula can be used:

=VLOOKUP (“Joe”, A1:C5, 3, 0)

The one constraint with VLOOKUP is that the Lookup Value must be in the first column of the Table Range. In the table above, we could not find the name of a person associated with a City or State using a VLOOKUP. So why use VLOOKUP, then? VLOOKUP is faster than INDEX/MATCH. Nearly twice as fast. For small data sets this doesn’t mean much, but when data sets become very large, functions that utilize less CPU overhead are optimal. In scenarios where there are lists of items with unique IDs, VLOOKUP can be the best option. In the absence of unique IDs, data must be looked at using other criteria and INDEX/MATCH is often the better solution. In the grand scale, once Excel begins to slow down due to all the VLOOKUPs and INDEX/MATCH functions, it may be time to invest in a real database.

TESTING VLOOK vs INDEX/MATCH

To compare the speed of each function, I used them to search for zip codes with data from a massive zip code database with over 100k records. If you replicate the same tests, you will likely get different results since the speed is also related to the speed of your computer, but the relative difference in speed between the two functions should be similar. In my testing, I found that Vlookup is faster than Index/Match.
Below is the code that was used in the testing.

Sub Test1()
Dim cc As CCounter, rng1 As Range
Set cc = New CCounter

cc.StartCounter
Debug.Print Application.WorksheetFunction.VLookup(“T0CF01C”, [ZipCodeMasterDB], 2, 0)
Debug.Print cc.TimeElapsed
Set cc = Nothing
End Sub

Sub Test2()
Dim cc As CCounter, rng1 As Range
Set cc = New CCounter

cc.StartCounter
Debug.Print Application.WorksheetFunction.Index([ZipCodeMasterDB].Columns(2), Application.WorksheetFunction.Match(“T0CF01C”, [ZipCodeMasterDB].Columns(1), 0))
Debug.Print cc.TimeElapsed
Set cc = Nothing

End Sub

Sub Test3()
Dim cc As CCounter, rng1 As Range
Set cc = New CCounter

cc.StartCounter
Debug.Print Application.WorksheetFunction.VLookup(“T0CF01C”, [ZipCodeMasterDB], Application.WorksheetFunction.Match(“City”, [ZipCodeMasterDB].Rows(1), 0))
Debug.Print cc.TimeElapsed
Set cc = Nothing
End Sub
Ccounter Class
Option Explicit

Private Type LARGE_INTEGER
lowpart As Long
highpart As Long
End Type

Private Declare Function QueryPerformanceCounter Lib “kernel32” (lpPerformanceCount As LARGE_INTEGER) As Long
Private Declare Function QueryPerformanceFrequency Lib “kernel32” (lpFrequency As LARGE_INTEGER) As Long

Private m_CounterStart As LARGE_INTEGER
Private m_CounterEnd As LARGE_INTEGER
Private m_crFrequency As Double

Private Const TWO_32 = 4294967296# ‘ = 256# * 256# * 256# * 256#

Private Function LI2Double(LI As LARGE_INTEGER) As Double
Dim Low As Double
Low = LI.lowpart
If Low < 0 Then
Low = Low + TWO_32
End If
LI2Double = LI.highpart * TWO_32 + Low
End Function

Private Sub Class_Initialize()
Dim PerfFrequency As LARGE_INTEGER
QueryPerformanceFrequency PerfFrequency
m_crFrequency = LI2Double(PerfFrequency)
End Sub

Public Sub StartCounter()
QueryPerformanceCounter m_CounterStart
End Sub

Property Get TimeElapsed() As Double
Dim crStart As Double
Dim crStop As Double
QueryPerformanceCounter m_CounterEnd
crStart = LI2Double(m_CounterStart)
crStop = LI2Double(m_CounterEnd)
TimeElapsed = 1000# * (crStop – crStart) / m_crFrequency
End Property

It must be pointed out that the counter class uses some declares so you may run into a 64bit compile error and may need to adjust the code to accommodate as such.

IN CONCLUSION

Both VLOOKUP and INDEX/MATCH have strengths and weaknesses. VLOOKUP is a shorter function that consumes less CPU when running while INDEX/MATCH is a more complex function that consumes more CPU, but allows for more lookup versatility. Each application is different and it is ultimiately up to the developer to determine which function is optimal.