In this blog post, we are going to take a look at how we can use a combination of the **INDEX** and **MATCH** functions, commonly referred to as **INDEX** **MATCH**, to find data in a range of cells.

Chances are that you are familiar with the **VLOOKUP** function. **VLOOKUP** is my favourite function. It can be used to quickly find data in a range of cells, but it does have its limitations. **VLOOKUP** uses the first column to search for a lookup value and can only return values to the right of the first column in the range of cells, but what if your lookup value isn’t located in the first column? This is where **INDEX** **MATCH** comes in. **INDEX** **MATCH** can return values to either the right or left of the column containing the lookup value, meaning it isn’t limited to using the first column in a range of cells to search for lookup values.

## A Quick VLOOKUP Refresher

The **VLOOKUP** function finds the row where a specific value is in the leftmost column within a range of cells. The **VLOOKUP** function then extracts the value from a cell on the same row but in a different column to the right in the same range of cells that you have specified.

The syntax of the **VLOOKUP** function is **VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]).**

Parameter | Description |
---|---|

lookup_value | This is the value that the VLOOKUP function searches for in the leftmost column of the table. |

table_array | This is the range of cells that the VLOOKUP function uses. The first column of the selected range of cells will be the one in which the lookup_value will be looked up. |

col_index_num | This is the number of the column to the right of the leftmost column that you want to extract the data from. The numbering starts at 1, being the leftmost column. |

range_lookup | This is an optional logical value. The default value is 1 (TRUE). This means that it will search for the closest numerical or alphabetical value to your lookup_value if it can`t find your exact search. The 0 value (FALSE) will specifically search for your lookup_value, and it will provide the #N/A value if it can`t find it. |

The goal of the **VLOOKUP** function is to search a **table_array** for a **lookup_value** and return the value in the corresponding number of columns (to the right), as specified by the **col_index_num**.

In the image above, the **VLOOKUP** function is used to find the position of a specific person in a range of cells (**table_array**). The name **Delilah** is used as the **lookup_value**. The **VLOOKUP** function searches the left-most column in the t**able_array**, column **A**, in this instance, highlighted in yellow to find the row that **Delilah** is on. The **VLOOKUP** function then uses the third column in the **table_array** because the **col_index_num** is **3**. Finally, the function then extracts the value from the cell in column **C** (in orange) and returns the value of “**Development Manager**.”

### Limitations of the VLOOKUP Function

One of the problems with **VLOOKUP**, as mentioned in the introduction to this blog post, is that you can only lookup values to the right of the first column in a range of cells. Imagine if the employee’s name (the **lookup_value**) in the previous example wasn’t in the first column of the range of cells

What if the employee’s name was in a column to the right of the column we want to return the value from? Remember that the **VLOOKUP** function can only return a value in a column to the right of the column containing the **lookup_value**.

## Hello INDEX MATCH

**INDEX** **MATCH** is more flexible than **VLOOKUP** in that it allows you to use any column in a range of cells as a lookup column; you can then return a value in a column either to the left or right of the reference column.

Why is this useful? Well, if you are working with an existing workbook, you might not be able to change the format of the data and the **lookup_value** might not be in the first column. Another example might be when the same range of cells is used by multiple formulas that lookup values in different columns.

**INDEX** **MATCH** is a combination of two functions, so it requires a little more thinking about when you use it. Don’t worry; in the following sections of this blog post, we are going to walk through these two functions individually; then we’re going to combine them.

## A Closer Look at MATCH

The **MATCH** function doesn’t return a value from a range of cells; it returns the relative row or column number of a lookup value in a range of cells.

The syntax of the **MATCH** function is **MATCH(lookup_value, lookup_array, [match_type]).**

Parameter | Description |
---|---|

look_value | This is the value that the MATCH function searches for in a range of cells. This parameter is required. |

lookup_array | This is the range of cells that the MATCH function searches for the lookup_value in . The range of cells can be a single row, a column of multiple rows, or a single column. This parameter is required. |

match_type | This parameter is a number, either -1, 0, or 1. The number is used to specify the match type. This parameter is optional; if it is omitted, the value defaults to 1. |

The following table describes the **match_type** options in detail.

match_type | Description |
---|---|

1 | Finds the smallest value that is greater than or equal to the lookup_value . For this to work correctly, the values in the lookup_array must be arranged in descending order: Z, ….C, B, A, …..4, 3, 2, 1 etc. |

0 | Find the first value in the lookup_array that exactly matches the lookup_value . The values in the lookup_array don't need to be sorted. |

or omitted | Finds the largest value that is less than or equal to the lookup_value . For this to work correctly, the values in the lookup_array must be arranged in ascending order: 1, 2, 3, 4, …., A, B, C,….Z etc. |

Let’s take a look at a few examples.

### Example 1 Match Type 1

In this first **MATCH** example, we are going to search the ages of the various employees in a range of cells to find the row number of a person who is **48**.

The range of cells containing the ages of the employees is **B2:B21**, highlighted in the image below.

In this example, we’re going to type our formula in cell **G2** and use cell **F2** to input the age of the employee we are looking for. In this instance, the age is **48**. To achieve this, we are going to use the following formula.

The formula returns the value **20**. This is because, **Fiona** who is the only person that is **48**, is **20** rows down the range of cells used for the l**ookup_array**.

In this formula, we also used **match_type 1**. This was because we sorted the ages in ascending order. If we had used **match_type -1**, then the formula returned a **#NA** error because the values were sorted in the wrong order to use **match_type -1**.

### Example 2 Match Type 0

In this second **MATCH** example, we are going to search a range of cells for the row number of the position “**Analyst**.” We want an exact match, so we‘re going to use **match_type 0**.

This time, the range of cells containing the position of the employees is **C2:C21**, highlighted in the image below.

Again, we’re going to type our formula in cell **G2** and use cell **F2** to input the position of the employee we are looking for, in this instance, an **Analyst**. To achieve this, we are going to use the following formula.

The formula returns the value **8**. This is because **Tom** is the only person with the position of **Analyst** which is **8** rows down the range of cells used for the **lookup_array**.

This time, we also used **match_type 0**. This was because we wanted an exact match, but also, we didn’t need to sort the positions in any particular way, ascending or descending order.

One thing to note is if we had more than one employee with the position of **Analyst**, the formula would have returned the row position of the first one it found.

### Example 3 Match Type 0

In this final example, we are going to search for the position ‘**Secretary**‘. Again, we want an exact match, so we’re going to use **match_type 0**. However, the position ‘**Secretary**‘ isn’t in our list.

If the **MATCH** function doesn’t find a matching **lookup_value** in the **lookup_array**, it returns a **#N/A** error.

Again, the range of cells containing the position of the employees is **C2:C21**, highlighted in the image below.

Using the same formula in cell **G2** and using cell **F2** to input the position of the employee we are looking for: in this instance, it is the position “**Secretary**“.

The formula returns the **#N/A** error because the position of “**Secretary**” isn’t in the range of cells used for the **lookup_array**.

### Some Things to Remember About The MATCH Function

The **MATCH** function doesn’t differentiate uppercase letters from lowercase letters when searching for the **lookup_value**. For example, to the **MATCH** function, “**today**“, “**TODAY**” and “**Today**” are the same.

One final note, you can use wildcard characters when using the **match_type 0**, and the **lookup_value** you are searching for is a text string. If you are not familiar with wildcard characters and are interested in finding out more, click here.

## A Closer Look at INDEX

The **INDEX** function can be used in either Reference form or Array form. We are only interested in Array form for this blog post. Please note, the syntax

The syntax of the **INDEX** function is **INDEX(array, row_num, [column_num])**.

Parameter | Description |
---|---|

array | When the range of cells has multiple rows and columns but only the row_num or column_num parameter, the INDEX function returns an array of cells that contains the entire row or column.When the range of cells has multiple rows and columns but only the row_num or column_num parameter, the INDEX function returns an array of cells that contains the entire row or column. |

row_num | This is the row number relative to the first row in the array parameter from which the value will be returned. This parameter is required if the column_num is omitted. |

column_num | This is the column number relative to the leftmost column in the array parameter from which the value will be returned. This parameter is optional unless the row_num is omitted. |

### Example 1 Single Row with one Parameter

In this first **INDEX** example, we are going to return a value from a range of cells in a single column of values using the **row_num** parameter.

The range of cells is **C2:C21**, containing the position for each person highlighted in the image below.

We’re going to type our formula in cell **G2** and use cell **F2** to input row number **10** for the **row_num** parameter of the **INDEX** function. To achieve this, we are going to use the following formula.

The formula returns the value “**Developer**“; this is because row **10** of the range of cells **C2:C21** used for the array parameter of the **INDEX** function is “**Developer**.”

### Example 2 Multiple Rows and Columns with both Parameters

In the second **INDEX** example, we are going to return a value in a range of cells that contains multiple rows and columns using both the **row_num** and **column_num** parameters.

The range of cells is **A2:D21**, and the range of cells containing all the employee data is highlighted in the image below.

Like before, we’re going to type our formula in cell **G2** and use cell **F2** to input row number **14** and for the **row_num** parameter and cell **F4** to input column number **3** for the **column_num** parameter of the **INDEX** function. To achieve this, we use the following formula.

The formula returns the value “**Senior Developer**“; this is because the intersection of row **14** and column **3** of the range of cells **A2:C21** used for the array parameter of the **INDEX** function is “**Senior Developer**.”

### Some Things to Remember About The INDEX Function

The **INDEX** function will return an array of values if you set either the **row_num** or **column_num** parameter to zero. To achieve this, the formula must be entered as an array formula. Array formulas are beyond the scope of this post, however. If you want to find out more about array formulas, click here.

The **row_num** and **column_num** parameters must reference a row and column within the range of cells used for the array parameter; otherwise, the **INDEX** function will return the **#REF!** error.

## Putting INDEX MATCH Together

### Example 1 The Same as VLOOKUP

In this first **INDEX MATCH** example, we’re going to repeat what we did in the **VLOOKUP** example at the beginning of the blog post. That is, we’re going to find the position of an employee using a person’s name as the lookup value. Thus, just like before, we’re going to search for the position of **Delilah**.

Let’s build up the formula one function at a time. Let’s start with the **MATCH** function.

We use the **MATCH** function to find out which row **Delilah** is on using the following formula.

Notice that the **lookup_array** range of cells is just the first column, **A2:A21**. This is okay since we are only interested in knowing the value returned by the **MATCH** function, which is the row number for **Delilah**.

We also use **match_type 0** because we want an exact match. Unless you have a very good reason to use the other match types, I would suggest using **match_type 0** whenever using the **INDEX** and **MATCH** functions together.

Now, let’s insert the **MATCH** function into our **INDEX** function in the following formula.

The **INDEX** function uses the row number returned by the **MATCH** function for the **row_num** parameter. The array parameter used in the **INDEX** function, which is the range of cells containing the positions of each employee, **C2:C21**.

The result is exactly the same as the **VLOOKUP** formula.

### Example 2 Different from VLOOKUP

Okay, for this second example, we’re going to do the reverse of the example above. That is, we’re going find the name of person using the position as the lookup value. The data remains unchanged, which means that our lookup value is in column **C**, which is in a column to the right of the value we want to return in column **A**. Remember, you can’t do this with **VLOOKUP**.

Like before, let’s build up the formula one function at a time. Again, let’s start with the **MATCH** function.

This time, we use the **MATCH** function to find out which row the position “**Development Manager**” is on using the following formula.

Notice that the **lookup_array** range of cells, this time, is in the third column, **C2:C21**. Remember, we are only interested in knowing the value returned by the **MATCH** function, the row number for “**Development Manager**”. We also use **match_type 0** again because we want an exact match.

Now, let’s insert the **MATCH** function into our **INDEX** function using the following formula.

The **INDEX** function uses the value returned by the **MATCH** function for the **row_num** parameter of the **INDEX** function. The array parameter used in the **INDEX** function is the range of cells containing the name of each employee, **A2:A21**.

And, there you go. We carried out a lookup that wasn’t dependent on the leftmost column of a range of cells. **INDEX** **MATCH** allows you to return values to either the left or right of your lookup value.

## Other Advantages of INDEX MATCH

### Inserting Columns

The **VLOOKUP** function doesn’t update the **col_index_num** when you insert a new column in the range of cells you are returning a value from. This means that if you are returning a value from, say, **col_index_num 3** (column **C**, in our example) and you decide to insert a new column between column **2** and **3** (columns **B** and **C** in our example), the **VLOOKUP** function will continue to return values from **col_index_num 3** (column **C** in our example). However, the column containing the position values for each employee is now **col_index_num 4** (column **D**).

**INDEX** **MATCH** can cope with this and will update the cell references .

### Copying Formulas

When you copy a formula containing the **VLOOKUP** function across columns, you could have problems depending on what you are trying to achieve.

**INDEX** **MATCH** uses cell reference instead of the **column_index_num** parameter used by **VLOOKUP**. You can use absolute and relative cell reference with **INDEX** **MATCH** so when copying a formula to other cells the references update. To learn more about absolute and relative cell reference, click here.

## Wrap-up

So, that’s how to use **INDEX** **MATCH**. This method is more flexible than **VLOOKUP** but requires a bit more thought

If you liked this post, please share it with your friends on Facebook or Twitter.

For other tools and resources for learning more about Excel, check out my resources page.

To get a copy of the example spreadsheet used in this blog post and receive a copy of our newsletter, sign up to our email list below.