What Are the INDEX and MATCH functions?

INDEX and MATCH are Excel lookup functions. While they are two entirely separate functions that can be used on their own, they can also be combined to create advanced formulas. The INDEX function returns a value or the reference to a value from within a particular selection. For example, it could be used to find the value in the second row of a data set, or in the fifth row and third column. While INDEX could very well be used alone, nesting MATCH in the formula makes it a bit more useful. The MATCH function searches for a specified item in a range of cells and then returns the relative position of the item in the range. For example, it could be used to determine that a specific name is the third item in a list of names.

INDEX and MATCH Syntax & Arguments

This is how both functions need to be written in order for Excel to understand them: =INDEX(array, row_num, [column_num])

array is the range of cells that the formula will be using. It can be one or more rows and columns, such as A1:D5. It’s required.row_num is the row in the array from which to return a value, such as 2 or 18. It’s required unless column_num is present.column_num is the column in the array from which to return a value, such as 1 or 9. It’s optional.

=MATCH(lookup_value, lookup_array, [match_type])

lookup_value is the value you want to match in lookup_array. It can be a number, text, or logical value that’s typed manually or referred to via a cell reference. This is required. lookup_array is the range of cells to look through. It can be a single row or a single column, such as A2:D2 or G1:G45. This is required. match_type can be -1, 0, or 1. It specifies how lookup_value is matched with values in lookup_array (see below). 1 is the default value if this argument is omitted.

INDEX Examples

In this first example, there are four INDEX formulas we can use to get different values:

=INDEX(A1:B2,2,2) looks through A1:B2 to find the value in the second column and second row, which is Stacy.=INDEX(A1:B1,1) looks through A1:B1 to find the value in the first column, which is Jon.=INDEX(2:2,1) looks through everything in the second row to locate the value in the first column, which is Tim.=INDEX(B1:B2,1) looks through B1:B2 to locate the value in the first row, which is Amy.

MATCH Examples

Here are four easy examples of the MATCH function:

=MATCH(“Stacy”,A2:D2,0) is searching for Stacy in the range A2:D2 and returns 3 as the result.=MATCH(14,D1:D2) is searching for 14 in the range D1:D2, but since it’s not found in the table, MATCH finds the next largest value that’s less than or equal to 14, which in this case is 13, which is in position 1 of lookup_array.=MATCH(14,D1:D2,-1) is identical to the formula above it, but since the array isn’t in descending order like -1 requires, we get an error.=MATCH(13,A1:D1,0) is looking for 13 in the first row of the sheet, which returns 4 since it’s the fourth item in this array.

INDEX-MATCH Examples

Here are two examples where we can combine INDEX and MATCH in one formula:

Find Cell Reference in Table

This example is nesting the MATCH formula within the INDEX formula. The goal is to identify the item color using the item number. If you look at the image, you can see in the “Separated” rows how the formulas would be written on their own, but since we’re nesting them, this is what’s happening:

MATCH(F1,A2:A5) is looking for the F1 value (8795) in the data set A2:A5. If we count down the column, we can see it’s 2, so that’s what the MATCH function just figured out.The INDEX array is B2:B5 since we’re ultimately looking for the value in that column.The INDEX function could now be rewritten like this since 2 is what MATCH found: INDEX(B2:B5, 2, [column_num]).Since column_num is optional, we can remove that to be left with this: INDEX(B2:B5,2).So now, this is like a normal INDEX formula where we’re finding the value of the second item in B2:B5, which is red.

Lookup By Row and Column Headings

In this example of MATCH and INDEX, we’re doing a two-way lookup. The idea is to see how much money we made off of Green items in May. This is really similar to the example above, but an extra MATCH formula is nested in INDEX.

MATCH(G1,A2:A13,0) is the first item solved in this formula. It’s looking for G1 (the word “May”) in A2:A13 to get a particular value. We don’t see it here, but it’s 5.MATCH(G2,B1:E1,0) is the second MATCH formula, and it’s really similar to the first but is instead looking for G2 (the word “Green”) in the column headings at B1:E1. This one resolves to 3.We can now rewrite the INDEX formula like this to visualize what’s happening: =INDEX(B2:E13,5,3). This is looking in the whole table, B2:E13, for the fifth row and third column, which returns $180.

MATCH and INDEX Rules

There are several things to keep in mind when writing formulas with these functions:

MATCH isn’t case sensitive, so uppercase and lowercase letters are treated the same when matching text values. MATCH returns #N/A for multiple reasons: if match_type is 0 and lookup_value isn’t found if match_type is -1 and lookup_array isn’t in descending order, if match_type is 1 and lookup_array isn’t in ascending order, and if lookup_array isn’t a single row or column. You can use a wildcard character in the lookup_value argument if match_type is 0 and lookup_value is a text string. A question mark matches any single character and an asterisk matches any sequence of characters (e.g., =MATCH(“Jo*",1:1,0)). To use MATCH to find an actual question mark or asterisk, type ~ first. INDEX returns #REF! if row_num and column_num don’t point to a cell within the array.

The MATCH function is similar to LOOKUP, but MATCH returns the position of the item instead of the item itself. VLOOKUP is another lookup function you can use in Excel, but unlike MATCH which requires INDEX for advanced lookups, VLOOKUP formulas only need that one function.