Skip to content

How to Perform a Two-Way Lookup

In this article, you will learn how to perform a two-way lookup in Excel. This method also works for Google Sheets.

What is a Two-Way Lookup

A two-way lookup is where you look up a piece of information based on two specified values. Take the following example.

Here I have some data in my spreadsheet. I’ve got a list of fruits and the price, color, and store for each.

Now let’s say that I want to look up the price, color, or store of a specific fruit. This is a two-way lookup because I am trying to retrieve one piece of information that is dependent on two variables. Those being:

  • The selected fruit
  • The selected metric (price, color, or store)

With these two pieces of information, I can perform a two-way lookup to get back the information that I want.

How to Set Up Your Data

To perform a two-way lookup in either Google Sheets or Excel, you first need to have your data laid out with one set of variables in a column and the other set in a row.

Here, I have the fruits listed in a column and the metrics in a row.

With your data set up like this, you will also need a space where you can specify your selections for each variable. In this case, I have my fruit selection in B1 and my metric selection in B2.

With your data set up like this, you can move on to performing your two-way lookup.

How to Perform the Lookup

To perform the two-way lookup, you will need to use the INDEX and MATCH functions.

The INDEX function will reference the data range, and the MATCH function will specify the position of the row and column of the return value based on the selections for the two variables.

Take a look at this example. I want my two-way lookup result in cell D2. So I enter the following formula into this cell.

=INDEX(B5:D12, MATCH(B1, A5:A12, 0), MATCH(B2, B4:D4, 0))

Here is how the formula works.

The INDEX function first references all of the data.

Then the second and third arguments of the INDEX function will indicate which row and column to pull the final value from. And this is where the MATCH function comes into play.

The MATCH function locates and returns the relative position of a specified value. So for the row number argument of the INDEX function, the MATCH function searches for the specified fruit value (from cell B1) and returns the position of that fruit from among the fruits listed in A5:A12.

The number representing the position is then used as the row number for the INDEX function.

For the column number argument of the INDEX function, the MATCH function searches for the specified metric (from cell B2) and returns the position of that metric.

The result is then used as the column number for the INDEX function’s third argument.

Finally, with the row and column number determined, the INDEX function returns the value at the intersection of that row and column.

And that is how you can perform a two-way lookup in either Microsoft Excel or Google Sheets.

See It In Action

To see an example of this in Excel, check out the video below.

This Post Has 2 Comments

  1. I used to think that the INDEX MATCH combo was just a complicated way of replicating VLOOKUP, but this is so helpful! I would love to know how you do the conditional formatting in the YouTube Short too!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top