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.

The INDEX and MATCH functions can be used every version of Excel.

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

Which Match Type to Use | |||
---|---|---|---|

Match Type |
What It Does |
Rule |
Example |

1 | Finds the largest value that's less than or equal to lookup_value. |
The lookup_array values must be placed in ascending order (e.g., -2, -1, 0, 1, 2; or A-Z;, or FALSE, TRUE. |
lookup_value is 25 but it's missing from lookup_array, so the position of the next smallest number, like 22, is returned instead. |

0 | Finds the first value that's exactly equal to lookup_value. |
The lookup_array values can be in any order. |
lookup_value is 25, so it returns the position of 25. |

-1 | Finds the smallest value that's greater or equal to lookup_value. |
The lookup_array values must be placed in descending order (e.g., 2, 1, 0, -1, -2). |
lookup_value is 25 but it's missing from lookup_array, so the position of the next largest number, like 34, is returned instead. |

Use *1* or *-1* for times when you need to run an approximate lookup along a scale, like when dealing with numbers and when approximations are okay. But remember that if you don't specify *match_type*, *1* will be the default, which can skew the results if you're really wanting an exact match.

## Example INDEX and MATCH Formulas

Before we look at how to combine INDEX and MATCH into one formula, we need to understand how these functions work on their own.

### INDEX Examples

=INDEX(A1:B2,2,2)

=INDEX(A1:B1,1)

=INDEX(2:2,1)

=INDEX(B1:B2,1)

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

=MATCH("Stacy",A2:D2,0)

=MATCH(14,D1:D2)

=MATCH(14,D1:D2,-1)

=MATCH(13,A1:D1,0)

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*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

=INDEX(B2:B5,MATCH(F1,A2:A5))

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

=INDEX(B2:E13,MATCH(H1,A2:A13,0),MATCH(H2,B1:E1,0))

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(H1,A2:A13,0)**is the first item solved in this formula. It's looking for*H1*(the word "May") in*A2:A13*to get a particular value. We don't see it here, but it's*5*.**MATCH(H2,B1:E1,0)**is the second MATCH formula, and it's really similar to the first but is instead looking for*H2*(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.

## Related Excel Functions

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.