Two-way Lookup Formula

This post explains that how to use two way lookup formula to find a value in a table or in a two dimensional range in excel. How to lookup a value in a table using given row and column with INDEX and MATCH functions. Or how to do perform a two way lookup with VLOOKUP function.

As the lookup functions in excel are only support to perform one-way lookups, so there is no built-in function to do a two-way lookup, the below will talk that how to create a new excel formula to perform two-way lookup in excel.

 

Two way lookup with index/match

If you want to look up a value in a table using both rows and columns, you can use a combination with the INDEX function and the MATCH function to create an excel formula. For example, if you have a salary table and you want to find jenny’s salary in Sep in the two dimensional range A1:C5, you can write down the following two-way lookup formula with INDEX function and MATCH function:

=INDEX(A1:C5,MATCH("jenny",A1:A5,0),MATCH("Sep",A1:C1,0))

Let’s see how this formula works:

The MATCH function returns the relative position of a lookup value in the range A1:A5 or A1:C1. And the match_type is set to 0, it means that the MATCH function lookup the first match of the value that is exactly equal to the lookup value.

= MATCH(“jenny”,A1:A5,0)

two way lookup excel

So the first MATCH function returns the first position of string “jenny” in the range A1:A5. It returns 3. It will goes into the INDEX function as its row_num argument.

= MATCH(“Sep”,A1:C1,0)

 

And the second MATCH function will return the position of the first occurrence of the string “Sep” in the range A1:C1. It returns 2 and it will goes into the INDEX function as its column_num argument.

two way lookup excel

So we can get the row number as 3 and the column number is 2 from the MATCH function.

 

=INDEX(A1:C5,3,2)

The INDEX function returns the value at the intersection of row 3 and column 2 in the range A1:C5.

two way lookup excel3

Two way lookup with VLOOKUP

You can also use the VLOOKUP function to build an excel formula to perform a two-way lookup in excel. You can try to use the following formula:

=VLOOKUP("jenny",A1:C5,MATCH("Sep",A1:C1,0),FALSE)

Let’s see how this formula works:

=MATCH(“Sep”,A1:C1,0)

As I said above, the MATCH function returns the relative position of the first occurrence of the string “Sep” in the range A1:C1. It returns 2. This value will go into the VLOOKUP function as its column_index_num argument.

 

=VLOOKUP(“jenny”,A1:C5,MATCH(“Sep”,A1:C1,0),FALSE)

two way lookup4

The VLOOKUP function lookup string “jenny” in the first column of the range A1:C5 and then returns the value in the same row based on the column_index_num value returned by the above MATCH function.


Related Formulas

  • Lookup Entire Row using INDEX/MATCH
    If you want to lookup entire row and then return all values of the matched row, you can use a combination of the INDEX function and the MATCH function to create a new excel array formula.
  • Extract the Entire Column of a Matched Value
    If you want to lookup value in a range and then retrieve the entire row, you can use a combination of the INDEX function and the MATCH function to create a new excel formula..…
  • Lookup the Next Largest Value
    If you want to get the next largest value in another column, you can use a combination of the INDEX function and the MATCH function to create an excel formula..

Related Functions

  • Excel VLOOKUP function
    The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])…
  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel MATCH function
    The Excel MATCH function search a value in an array and returns the position of that item.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….
Related Posts
VLOOKUP From Another Sheet Not Working
vlookup from another sheet not working3

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you reasons why your VLOOKUP formula is not working ...

Case Sensitive Lookup with SUMPRODUCT and EXACT

Today, we will show you how to use SUMPRODUCT and EXACT to perform a case sensitive exact match. In this article, we provide a simple example to calculate bonus for employees whose names are case-sensitive. If you meet similar scenarios ...

Basic Usage of INDEX & MATCH – Case Sensitive Lookup

In Excel, INDEX function and MATCH function are often used together for retrieving data from a particular position. MATCH function is one of Excel lookup & reference functions that can perform approximate match or exact match by setting different match ...

Basic Rates Calculation by VLOOKUP Based on Weight Band

Microsoft Excel provides many functions that can execute logical test, search data, return current date and something else. They are very useful in daily work. And Excel VLOOKUP function is one of Excel most frequently used functions. It belongs to ...

Basic Grade Calculation by VLOOKUP Function – Approximate Match

In Excel, except combination INDEX+MATCH, we can also apply other functions to search data, for example VLOOKUP function. Like MATCH function, VLOOKUP function is one of Excel lookup & reference functions that can perform approximate match or exact match by ...

Basic Discount Calculation with VLOOKUP Function

In Excel, except combination INDEX+MATCH, we can also apply other functions to search data, for example VLOOKUP function. Like MATCH function, VLOOKUP function is one of Excel lookup & reference functions that can perform approximate match or exact match by ...

Basic Usage of INDEX & MATCH – Exact Match
Basic Usage of INDEX & MATCH - Exact Match 1&2

In Excel, INDEX function and MATCH function are often used together for returning value or cell reference or range reference from specified position. And MATCH function is one of Excel lookup & reference functions that can perform approximate match or ...

Basic Usage of INDEX & MATCH – Approximate Match

In Excel, INDEX function and MATCH function are often used together for returning value or cell reference or range reference from specified position. And MATCH function is one of Excel lookup & reference functions that can perform approximate match by ...

Approximate Match with Multiple Criteria by INDEX & MATCH
Approximate Match with Multiple Criteria by INDEX & MATCH 1 & 2

In Excel, INDEX function and MATCH function are often used together for returning data from specific position. And MATCH function is one of Excel lookup & reference functions that can return approximate value by setting match type. Above all, through ...

How to Count Cells Not Equal to One of Many Things in Excel
count cells not equal many things10

This post will guide you how to count the number of cells that are not equal to many things in a given range cells using a formula in Excel 2013/2016. You can easily to count cells not equal to a ...

Sidebar