March 6, 2021

Index Match and Xlookup

How to replace index match functions in VBA with Xlookup.

Watch the video below:

We have learnt about the use of Index, Match and Xlookup functions in earlier videos.

What is the Index function in Excel? The INDEX function returns a value or the reference to a value from within a table or range. We can use the INDEX function in two ways.

If we wish to return the value of a specified cell or array of cells, we use the Array form as shown in the video.

If we wish to return a reference to specified cells, we use the Reference form which is not discussed here.

INDEX(array, row_num, [column_num])

The array form of the INDEX function has the following arguments:

array: This argument is required. It consists of a range of cells or an array constant.

If our array consists of only one row or column, the corresponding row_num or column_num argument is optional.

If our array consists of more than one row and more than one column, and only row_num or column_num is used, the INDEX function returns an array of the entire row or column in array.

row_num: This is also a required parameter. It represents the row in the array from which to return a value. If row_num is omitted, column_num is required.

column_num: This is an optional parameter. It represents the column in array from which to return a value. If column_num is omitted, row_num is required.

If both the row_num and column_num arguments are used, the INDEX function returns the value in the cell at the intersection of row_num and column_num.

The row_num and the column_num must point to a cell within array else the INDEX function returns a #REF! error.

Important: If we use the current version of Microsoft 365, then we can input the formula in the top-left-cell of the output range and press ENTER to confirm the formula as a dynamic array formula. Otherwise, we have to enter the formula as a legacy array formula and then press CTRL+SHIFT+ENTER to confirm it. Excel inserts curly brackets at the beginning and end of the formula for us automatically.


Array Form of INDEX Function
Array form of INDEX function

What is the MATCH function in Excel? The MATCH function helps us to search for a specified item in a range of cells, and then returns the relative position of that item in the range. In the example image below we can observe that the name “Zuschuss Donatelli” is in row 10 of the specified array of cells A2:A78.

MATCH Function
MATCH Function

MATCH(lookup_value, lookup_array, [match_type])

The MATCH function syntax has the following arguments:

lookup_value: This arguments is required. It represents the value that we wish to match in the lookup_array. For example, when we look up a person’s number in a telephone book, we use the person’s name as the lookup value to get the telephone number.

The lookup_value parameter can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value. So we write “Zuschuss Donatelli” or A11 as the lookup_value as shown in the image above.

lookup_array: This arument is required. It represents the range of cells being searched, for example, A2:A78.

match_type: This parameter is optional. It can take the numbers -1, 0, or 1. The default value for this argument is 1.

The table below describes how the function Excel matches lookup_value with values in lookup_array.

1 or omittedMATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument are to be placed in ascending order, for example: …-2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE.
0MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.
-1MATCH finds the smallest value that is greater than or equal tolookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, …2, 1, 0, -1, -2, …, and so on.
Match_Type Argument

MATCH returns the position of the matched value within lookup_array, not the value itself. For example, MATCH(“Tom”,{“Tom”,”Kate”,”Harry”},0) returns 1, which is the relative position of “Tom” within the array {“Tom”,”Kate”,”Harry”}.

MATCH does not distinguish between uppercase and lowercase letters while matching text values.

If MATCH does not find a match, it returns the #N/A error value.

If match_type is 0 and lookup_value is a text string, we can use the wildcard characters — the question mark (?) and asterisk (*) — in the lookup_value argument. A question mark equals a single character; an asterisk equals a sequence of characters. If we wish to seach an actual question mark or asterisk, we type a tilde (~) before the character.

Using INDEX and MATCH in combination:

INDEX MATCH Functions Combination

Below is the VBA code using Index and Match functions:

Sub IndexMatch()
Dim sheet1LastRow As Long, sheet2LastRow As Long, i As Long
Dim IndexRange As Range, MatchRange As Range

sheet1LastRow = Application.WorksheetFunction.CountA(sheet1.Range(“A:A”))
sheet2LastRow = Application.WorksheetFunction.CountA(sheet2.Range(“A:A”))
‘MsgBox sheet1LastRow & “,” & sheet2LastRow

Set IndexRange = sheet2.Range(“A2:A” & sheet2LastRow)
Set MatchRange = IndexRange.Offset(0, 1)

For i = 2 To sheet1LastRow
On Error Resume Next
sheet1.Range(“B” & i).Value = Application.WorksheetFunction.Index(IndexRange, _
Application.WorksheetFunction.Match(sheet1.Range(“A” & i).Value, MatchRange, 0))

Next i

End Sub

For details of the use of the Xlookup function, watch this training video.