Hello, and welcome to this blog post, where you will learn how to perform a two-way lookup using the functions **INDEX MATCH MATCH**. No, that’s not a typo; you will use the **MATCH** function twice.

In this blog post, you’ll learn what a two-way lookup is, why it is useful, how to manage some of the error messages you may receive, and work through some examples as you learn how to use this combination of functions.

Click the button below to download the free example spreadsheet used in this blog post and companion PDF guide.

# What Is a Two-Way LOOKUP

You may be familiar with the **VLOOKUP**, **HLOOKUP**, and the combination of **INDEX** and **MATCH** functions in a formula to carry out one-way lookups so that you can find data in a worksheet. If you are not familiar with these functions, please see my **VLOOKUP** post here, and my **INDEX MATCH** post here.

**VLOOKUP**, **HLOOKUP**, and a combination of **INDEX** and **MATCH** functions can be extremely useful. However, these methods of carrying out lookups have one thing in common; they are one-way lookups. You can look up a value in either a column or a row, but not both.

There is a way to get around this and lookup values in both a row and a column at the same time. This is sometimes called a two-way or matrix lookup, the method we are going to look at in this blog post use a combination of the **INDEX MATCH MATCH** functions.

## Why Is It Useful

Being able to look up values in both a row and a column at the same time can be useful. Imagine you have a table of sales figures, where the column headings are the months of the year, and in column A of each row, you have the year, like the image below.

Now, if you want to find the sales figures for June 2013, you could simply carry out a two-way lookup for the month and the year.

# The Functions

Let’s do a quick recap of the two key functions we are going to use in this two-way lookup method.

## The MATCH Function

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

Using out previous sale figures example. We can use the **MATCH** function to return the relative column position for June. To do this we would use the following formula.

**=MATCH(F2,A4:M4,0)**

Let’s break this formula down:

**lookup_value**– is a cell reference to cell**F2**, which contains the value June, the month we are looking for.**lookup_array**– is the range of cells**A4:M4**, the range of cells containing the month names.**match_type**– is 0 because we want an exact match.

The formula returns the value 7 because June is in the 7^{th} column to the right in the range of cells **A4:M4**.

Later, when we perform a two-way lookup, we are going to use the **MATCH** formula twice: once to return the row number and once to return the column number.

## The INDEX Function

The **INDEX** function can be used when dealing with references or arrays. We are only interested in arrays in this blog post

**Please note that the syntax below is for arrays only.**

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

Using our earlier example, let’s look at how to return the sales figures for June 2013 using just the **INDEX** function. To do this, we use the following formula.

**=INDEX(A4:M21,F2,G2)**

Let’s break down this formula:

**array**– the range of cells from which we will retrieve the sales figures. In this instance, our range of cells is**A4:M21**.**row_num**– a cell reference to cell F2, which contains the value 15. This refers to the 15th row in our range of cells**A4:M21**. This row contains the sales figures for each month of the year 2013.**column_num**is a cell reference to cell**G2**, which contains the value 7. This is the 7^{th}column across in our range of cells**A4:M21**. This column contains the sales figures for June of each year.

The formula returns the value $155,638, which is the sales figure for June 2013 found at the intersection of row 15 and column 7 in our range of cells.

# Putting Them Together

Okay, now let’s put all this together. We’re going to use the **MATCH** function to return the relative row and column numbers for the sales figure value we want. We’ll then pass the values returned by the **MATCH** functions to the **INDEX** function. The **INDEX** function will then return the value at the intersection of the row and column specified by the **MATCH** functions. The formula we are going to use to achieve this is:

**=INDEX(A4:M21,MATCH(F2,A4:A21,0),MATCH(G2,A4:M4,0))**

Let’s break this down. The first **MATCH** expression, **MATCH(F2,A4:A21,0)**, returns the value 15, which is the relative row position of the value in cell **F2**, which in this instance is the year 2013.

The second **MATCH** expression, **MATCH(F2,A4:A21,0)**, returns the value 7, which is the relative column position for the value in cell **G2**, which in this instance is the month of June.

The values returned by the **MATCH** functions are passed to the **INDEX** function as the **row_num** and **column_num** parameters. Therefore, the **INDEX** function, using the values returned by two **MATCH** functions, returns the cell value $155,638 which is at the intersection of row 15 and column 7 in the range of cells **A4:M21**.

The result is the same as using the formula **=INDEX(A4:M21,15,7)**.

You might be thinking, “so what, this seems like a lot of work.” However, here is the clever part: if we type 2009 into cell **F2** and September into cell **G2**, we get the sales figures for September 2009 without changing the formulas.

# Errors

One of the key problems you might have is that someone might enter an invalid value into either the month input cell **G2**, or year input cells **F2**. For example, if we enter the value 2020 in year cell **F2**, you get the **#N/A** error as shown in the image below.

To get around this, we can wrap our **INDEX MATCH MATCH** formula with the **IFERROR** function.

The **IFERROR** function is one of the methods that can be used to manage errors.

**The syntax of the IFERROR** function is **IFERROR(value, value_if_error)**.

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

value | The value that is displayed if there is no error. This is generally some kind of function that is calculating a value. |

value_if_error | The value that should be used if an error occurs. |

When we wrap our formula with the **IFERROR** function, we need to decide what to do if an error is returned. For now, let’s just display the message **“Incorrect Value Entered”**. When we’ve finished, our formula will look like this:

**=IFERROR(INDEX(A4:M21,MATCH(F2,A4:A21,0),MATCH(G2,A4:M4,0)),**

**“Incorrect Value Entered”)**

Let’s break the **IFERROR** portion of the formula down:

**value**– if no error is detected then the**IFERROR**function return the value of the**INDEX(A4:M21,MATCH(F2,A4:A21,0),MATCH(G2,A4:M4,0)**expression which is unchanged from the formula used in the previous example.**value_if_error**– if a value is detected, then the**IFERROR**function returns the value “Incorrect Value Entered”.

Now, if we use this formula and enter incorrect values into either the year cell **F2**, for example 2020, or the month cell **G2**, for example dog, then the message **“Incorrect Value Entered”** is displayed.

We could also use the **IFNA** function. However, when writing more complex formulas, it is sometimes easier just to use the **IFERROR** function to capture all error types. This is especially true if you are less familiar with writing complex formulas.

For more information on the **IFERROR** and the **IFNA** function, click here.

# Making It a Bit More Dynamic with Dropdown Lists

We can make things a little more dynamic using some dropdown lists to select the Month and Year value as shown in the image below.

Not only does this make the workbook easier to use, but it can also prevent errors as you limit the values that can be entered to a predefined list.

To do this, we create name ranges. The first name range is for years. To do this, select cells **A5:A21** and type **“SalesYear”** in the name box and press enter as shown in the image below.

**NOTE: we don’t select A4 as this is a blank cell.**

Once this is done, press enter. Now create the second name range for the months. Select cells **B4:M4** and type **“SalesMonth”** in the name box and press enter as shown in the image below.

Now, to make the dropdown lists.

Click in cell **F2**, click the **Data** tab on the ribbon, and click the **Data Validation** button. When you have clicked the **Data Validation** button, the **Data Validation** dialogue should appear, as shown in the image below. (Versions for both Windows and Mac)

From the **Allow:** drop-down menu select **List**.

In the **Source:** textbox, type **=SalesYear** and click OK.

When you’ve done this, you should have your first drop-down list in cell **F2**, where you can select the year.

We will now repeat this for the month. Click on cell **G2**, and click the **Data Validation** button so that the **Data Validation** dialogue appears. Select **List** from the **Allow:** drop-down menu, now type **=SalesMonth** in the **Source:** textbox and click OK.

Now that this is done, you should have two drop-down lists that allow you to display the sales figures by selecting the month and the year. Additionally, this now prevents incorrect values from being entered.

To find out more about managing name ranges, click here.

# Wrap-Up

Okay, that’s it for this blog post. I hope you found it useful. As you can see, two-way lookups can be extremely useful when you need to search for information based on two values. While the **INDEX MATCH MATCH** combination of functions seems complicated at first, hopefully, you can see that it is worth taking the time to learn this method. When you combine them with drop-down lists, you soon end up with a very powerful search tool.

If you want to download your copy of the **INDEX MATCH MATCH** Guide and Example Spreadsheet fill out the form below.