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

Extract Unique Items From A List

This post will guide you how to extract unique itmes from a given list in Microsoft Excel. How to create a newly formula to get unique values from a range cells in Excel. The unique list of items is the ...

Extract all the matches with helper Column

With Excel's powerful functions IF, INDEX, and MATCH, we can find exactly what you're looking for with a few clicks of the mouse. This step-by-step tutorial will show how easy it is to extract data using these tools and more! ...

VLOOKUP From Another Sheet Not Working

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

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

Sidebar