How to Use the INDEX MATCH Functions to Find Data

How to Use the INDEX MATCH Functions to Find Data

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]).

ParameterDescription
lookup_valueThis is the value that the VLOOKUP function searches for in the leftmost column of the table.
table_arrayThis 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_numThis 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_lookupThis 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.

Explaining VLOOKUP

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 table_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_valueThis is the value that the MATCH function searches for in a range of cells. This parameter is required.
lookup_arrayThis 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_typeThis 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_typeDescription
1Finds 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.
0Find 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 omittedFinds 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.

Ages of the employees cells B2-B21

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.

MATCH Formula Example 1

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

MATCH function Example 1

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.

Position of the employees cells C2-C21

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.

MATCH Formula Example 2

 

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.

MATCH function Example 2

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.

Position of the employees cells C2-C21 Example 3

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

MATCH Formula Example 2

 

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

MATCH function Example 3

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]).

ParameterDescription
arrayWhen 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_numThis 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_numThis 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.

Position of the employees cells C2-C21 INDEX Example 1

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.

INDEX Formula Example 1

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

INDEX function Example 1

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.

Position of all employee data A2-D21 INDEX Example 2

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.

INDEX Formula Example 2

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

INDEX function Example 2

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.

INDEX MATCH Formula Example 1

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.

INDEX MATCH Example 1 MATCH Function

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.

INDEX MATCH Example 1 INDEX MATCH 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.

INDEX MATCH Example 1 INDEX MATCH Function

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.

INDEX MATCH Example 2 MATCH Function

 

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.

INDEX MATCH Example 2 MATCH Functon.

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

INDEX MATCH Example 2 INDEX MATCH Function

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.

INDEX MATCH Example 2 INDEX MATCH Function

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.

 

Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Reddit