What is INDEX MATCH?
INDEX MATCH is a clever way to perform a two-way lookup in Excel by combining the power of the INDEX and MATCH functions. It is used as a workaround for the limitations of VLOOKUP, and offers great flexibility once you understand how it works.
First, let's look at what VLOOKUP is meant to solve, and where it falls short.
Download your free Excel INDEX MATCH practice file!
Use this free template to practice along with the INDEX MATCH tutorial.
How VLOOKUP works
Cells F2 to G5 act like a simple query tool to extract information from the dataset in cells A2 to D9. VLOOKUP uses the Product Number which is manually entered in G2 to return each respective value, and the last argument (FALSE) in the VLOOKUP syntax ensures that Excel looks for an exact match between the product number and the first column in the source dataset.
This is great, but there are certain restrictions within VLOOKUP.
Limitations of VLOOKUP
- With VLOOKUP, the lookup value must always be in the first column of the lookup array. VLOOKUP cannot return a value that is to the left of the lookup value in the source array. It sounds simple, but it’s true, and it becomes a really big problem with data that we cannot change or reorganize. For example, if the query above was designed for the user to enter the Paper Type, VLOOKUP would be unable to return the Product Number with the current layout of the dataset.
- When approximate matches are accepted, (last argument omitted or TRUE,) the lookup values in the source data must be sorted in ascending order. Lists sorted in descending order will return incorrect results. Again, you may not have this much control over the data in your lookup array.
If you use VLOOKUP regularly, you would have encountered these problems and may have just decided to live with them. Learning how to use INDEX MATCH is a great workaround.
Let’s break down both functions individually.
How to use the MATCH function
The MATCH function is used to determine the position number of a known value within a range of cells. The MATCH function has three possible arguments, with the following syntax:
=MATCH(lookup_value, lookup_array, [match_type])
- Lookup_value is the known value which you will be using to conduct a lookup, or search. This can be a value or cell reference.
- Lookup_array is the range of cells where the lookup value will be found.
- Match_type is a setting which tells Excel whether you will accept a near-match if the lookup_value is not found in the lookup array.
- Match type 0 is for an exact match.
- Match type 1 accepts a near-match, rounded down to the next available value.
- Match type -1 accepts a near-match rounded up to the next available value.
- Match_type is optional. If omitted, a match type of 1 is assumed.
For instance, if we wanted to know the position number of the word “matte” within the range B2 to B9 below.
=MATCH(“matte”,B2:B9,0)
Note that in the example above, the value “matte” was typed directly into the formula between double quotation marks. If the lookup value is a cell reference, no double quotes are used.
Note also that the MATCH function is not case sensitive.
How to use the INDEX function
The INDEX function returns a value or cell reference from within a table or range. The INDEX function has two formats - the array format and the reference format. The array format is used when we want to return the value that is found in the result cell. The reference format is used when we want Excel to return the cell reference of the result cell (e.g. D17). We’ll focus on the array format, since this is the format you’ll need to learn for the INDEX/MATCH combo.
The syntax of the INDEX array function is
=INDEX(array, row_num, [column_num])
- Array refers to the range or array containing the data to be indexed. This argument is required.
- Row_num is the row number where the cell containing the return value is to be found. Row_num can be omitted if the array consists of only one row. If row_num is omitted, column_num is required.
- Column_num is the column number where the cell containing the return value is to be found. Column_num is optional, but is required if row_num is omitted.
In the spreadsheet below, we want to display the value which is in the third row, second column of the array by using the INDEX function.
=INDEX(A2:D9,3,2)
The third item in column 2 is identified as the value “Matte” and returned as the output in cell F2.
Combining the INDEX and MATCH functions
As mentioned before, the INDEX and MATCH formulas, when nested, can perform a lookup which accomplishes what the VLOOKUP does and more. Nesting a formula means using one entire formula as an argument of another function.
We can apply that to our simple query which extracts values from a dataset using the Product Number as an input, or lookup value.
We will use MATCH to determine where in the list the product number falls (position number), and we will use INDEX to return the values from the corresponding columns (Paper Type, Price, or In Stock,) by their column numbers.
Shown separately, each function would read as follows:
=MATCH(G2, A2:A9,0)
This performs a search for the value in cell G2 within the A2 to A9 range, and requires an exact match. It returns the position number of that value.
=INDEX(A2:D9, row_num, 2)
This returns the value from the array A2 to D9 which intersects the row number found in the MATCH formula, and column number 2 (Paper Type). The MATCH formula will be used as the row_num argument.
=INDEX(A2:D9,MATCH(G2,A2:A9,0),2)
The formula in G4 and G5 are identical, except for the last argument, which are 3 and 4 respectively, to represent each column number.
To be fair, this does seem quite complicated when we consider that VLOOKUP already does this in a much more straightforward way, and with only four arguments. However, at the outset, we pointed out that VLOOKUP could not return a value which was located to the left of the lookup value.
If we change the known value so that the Paper Type is now our lookup_value, VLOOKUP returns a #NA (Value Not Available) error. Let’s see how INDEX MATCH handles this.
=INDEX(A2:D9, MATCH(G2, B2:B9,0),1)
Lookups to the left or right are no problem for INDEX MATCH, because the MATCH lookup_array stands on its own, and is not connected to the INDEX array.
In this way, INDEX MATCH can also be used as a substitute for HLOOKUP, which is the horizontal version of the VLOOKUP function.
Let's look at a more advanced application of the INDEX MATCH solution.
INDEX MATCH advanced example
Test-takers have entered their Student IDs on their test papers, which are then scored and the grades entered in column D. We want to look up the Student ID in the array A2 to C7, but VLOOKUP is unable to do so. The student names are displayed to the left of the ID numbers because the list is arranged alphabetically. That is our first problem.
We also want to assign a letter grade based on each student’s test score. The test score chart (F2 to G6) is sorted in descending order, which is another thing VLOOKUP cannot handle.
The MATCH function will be used to determine the row number of the INDEX function.
=MATCH(B11,$C$2:$C$7,0)
(The range C2 to C7 will be copied, so we can use $ to make the references fixed. Learn more about Absolute and Mixed References.)
We used the match type 0 to ensure that only an exact match will be returned.
The MATCH function located Student ID 114125 in position 5 of the lookup array.
Now we just need to link that to the corresponding student name.
=INDEX($A$2:$C$7,MATCH(B11,$C$2:$C$7,0),1)
(The range A2 to C7 will be copied, so we can use $ to make the references fixed. Learn more about Absolute and Mixed References.)
The MATCH formula has been inserted, or nested, within the INDEX function as a way to find out what row is to be returned in each instance. The first column of the array contains the value we want returned, so the last argument of the INDEX function (column_num), is 1.
This formula can now be copied to the remaining rows in column C.
Determining the letter grade for each student will follow a similar pattern, with one exception. Since the grading chart only lists five values, students’ test scores may not be an exact match, therefore the match_type in the MATCH formula must be adjusted to accept an approximate match.
Array Type |
MATCH formula match_type |
Exact match |
0 |
Approximate match, to closest value smaller than lookup value |
1 |
Approximate match, to closest value larger than lookup value |
-1 |
Our MATCH and INDEX formulas would read
- MATCH(D11,$F$2:$F$6,-1)
- INDEX($F$2:$G$6,nested MATCH formula,2)
When nested, the complete formula would be:
=INDEX($F$2:$G$6,MATCH(D11,$F$2:$F$6,-1),2)
So although VLOOKUP was made to simplify the common task of finding items in a table, we can’t discard INDEX MATCH for more advanced tasks.
Here’s something you might want to know - Excel has rolled out XLOOKUP as the fix for the above complaints about VLOOKUP’s limitations. The drawback is that for now it’s only available on Excel 365, so if you have an older version, you’ll definitely want to understand and master INDEX MATCH.
Master Excel today
Learn more essential Excel skills and boost your productivity with the GoSkills Microsoft Excel - Basic and Advanced course.
Level up your Excel skills
Become a certified Excel ninja with GoSkills bite-sized courses
Start free trial
No comments
LoginSign upPlease login or sign up to comment