How to Perform a Two-Way LOOKUP Using INDEX MATCH MATCH

How To Perform a Two-Way Lookup Using INDEX MATCH MATCH

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.

Rows and Columns

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.

Row and Column Intersection

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

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.

MATCH Function Only

The formula returns the value 7 because June is in the 7th 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]).

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.

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 7th column across in our range of cells A4:M21. This column contains the sales figures for June of each year.

Index Function Only-v1

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.

First MATCH Function Only v1

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.

 Second MATCH Function Only v1

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.

Index Match Match Formula v1

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.

Index Match Match Formula Example 2

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.

NA Error v2

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
valueThe value that is displayed if there is no error. This is generally some kind of function that is calculating a value.
value_if_errorThe 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.

IFEERROR Function

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.

Dropdown Menu GIF

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.

SalesYEar Name Range

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.

SalesMonth Name Range

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)

Open Data Validation dialogue Windows

Data Validation Dialogue – Windows

Open Data Validation Dialogue

Data Validation Dialogue – Mac

From the Allow: drop-down menu select List.

Select List Allow Dropdown Menu Data Validation dialogue

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

Type in Source Text Box Data Validation dialogue

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

Year Dropdown List

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.

Select List Allow Dropdown Menu Data Validation dialogue

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.

Dropdown list Year and Month

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.

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