*The tutorial showcases a few different formulas to perform two dimensional lookup in Excel. Just look through the alternatives and choose your favorite :)*

When searching for something in your Excel spreadsheets, most of the time you'd look up vertically in columns or horizontally in rows. But sometimes you need to look across both rows and columns. In other words, you aim to find a value at the intersection of a certain row and column. This is called **matrix lookup** (aka **2-dimensional** or **2-way lookup**), and this tutorial shows how to do it in 4 different ways.

The most popular way to do a two-way lookup in Excel is by using INDEX MATCH MATCH. This is a variation of the classic INDEX MATCH formula to which you add one more MATCH function in order to get both the row and column numbers:

INDEX (*data_array*, MATCH (*vlookup_value*, *lookup_column_range*, 0), MATCH (*hlookup value*, *lookup_row_range*, 0))

As an example, let's make a formula to pull a population of a certain animal in a given year from the table below. For starters, we define all the arguments:

*Data_array*- B2:E4 (data cells, not including row and column headers)*Vlookup_value*- H1 (target animal)*Lookup_column_range*- A2:A4 (row headers: animal names) - A3:A4*Hlookup_value*- H2 (target year)*Lookup_row_range*- B1:E1 (column headers: years)

Put all the arguments together and you will get this formula for two-way lookup:

`=INDEX(B2:E4, MATCH(H1, A2:A4, 0), MATCH(H2, B1:E1, 0))`

While it may look a bit complex at first glance, the formula's logic is really straightforward and easy to understand. The INDEX function retrieves a value from the data array based on the row and column numbers, and two MATCH functions supply those numbers:

`INDEX(B2:E4, row_num, column_num)`

Here, we leverage the ability of MATCH(lookup_value, lookup_array, [match_type]) to return a **relative position** of *lookup_value* in *lookup_array*.

So, to get the row number, we search for the animal of interest (H1) across the row headers (A2:A4):

`MATCH(H1, A2:A4, 0)`

To get the column number, we search for the target year (H2) across the column headers (B1:E1):

`MATCH(H2, B1:E1, 0)`

In both cases, we look for exact match by setting the 3rd argument to 0.

In this example, the first MATCH returns 2 because our vlookup value (Polar bear) is found in A3, which is the 2^{nd} cell in A2:A4. The second MATCH returns 3 because the hlookup value (2000) is found in D1, which is the 3^{rd} cell in B1:E1.

Given the above, the formula reduces to:

`INDEX(B2:E4, 2, 3)`

And return a value at the intersection of the 2^{nd} row and 3^{rd} column in the data array B2:E4, which is a value in the cell D3.

Another way to do a two-dimensional lookup in Excel is by using a combination of VLOOKUP and MATCH functions:

VLOOKUP(*vlookup_value*, *table_array*, MATCH(*hlookup_value*, *lookup_row_range*, 0), FALSE)

For our sample table, the formula takes the following shape:

`=VLOOKUP(H1, A2:E4, MATCH(H2, A1:E1, 0), FALSE)`

Where:

*Table_array*- A2:E4 (data cells including row headers)*Vlookup_value*- H1 (target animal)*Hlookup_value*- H2 (target year)*Lookup_row_range*- A1:E1 (column headers: years)

The core of the formula is the VLOOKUP function configured for exact match (the last argument set to FALSE), which searches for the lookup value (H1) in the first column of the table array (A2:E4) and returns a value from another column in the same row. To determine which column to return a value from, you use the MATCH function that is also configured for exact match (the last argument set to 0):

`MATCH(H2, A1:E1, 0)`

MATCH searches for the value in H2 across the column headers (A1:E1) and returns the relative position of the found cell. In our case, the target year (2010) is found in E1, which is 5^{th} in the lookup array. So, the number 5 goes to the *col_index_num* argument of VLOOKUP:

`VLOOKUP(H1, A2:E4, 5, FALSE)`

VLOOKUP takes it from there, finds an exact match for its lookup value in A2 and returns a value from the 5^{th} column in the same row, which is the cell E2.

Recently Microsoft has introduced one more function in Excel that is meant to replace all existing lookup functions such as VLOOKUP, HLOOKUP and INDEX MATCH. Among other things, XLOOKUP can look at the intersection of a specific row and column:

XLOOKUP(*vlookup_value*, *vlookup_column_range*, XLOOKUP(*hlookup_value*, *hlookup_row_range*, *data_array*))

For our sample data set, the formula goes as follows:

`=XLOOKUP(H1, A2:A4, XLOOKUP(H2, B1:E1, B2:E4))`

The formula uses the ability of XLOOKUP to return an entire row or column. The inner function searches for the target year in the header row and returns all the values for that year (in this example, for year 1980). Those values go to the *return_array* argument of the outer XLOOKUP:

`XLOOKUP(H1, A2:A4, {22000;25000;700}))`

The outer XLOOKUP function searches for the target animal across the column headers and returns the value in the same position from the return_array.

The SUMPRODUCT function is like a Swiss knife in Excel – it can do so many things beyond its designated purpose, especially when it comes to evaluating multiple criteria.

To look up two criteria, in rows and columns, use this generic formula:

SUMPRODUCT(*vlookup_column_range* = *vlookup_value*) * (*hlookup_row_range* = *hlookup_value*), *data_array*)

To perform a 2-way lookup in our dataset, the formula goes as follows:

`=SUMPRODUCT((A2:A4=H1) * (B1:E1=H2), B2:E4)`

The below syntax will work too:

`=SUMPRODUCT((A2:A4=H1) * (B1:E1=H2) * B2:E4)`

At the heart of the formula, we compare two lookup values against the row and column headers (the target animal in H1 against all animal names in A2:A4 and the target year in H2 against all years in B1:E1):

`(A2:A4=H1) * (B1:E1=H2)`

This results in 2 arrays of TRUE and FALSE values, where TRUE's represent matches:

`{FALSE;FALSE;TRUE} * {FALSE,TRUE,FALSE,FALSE}`

The multiplication operation coerces the TRUE and FALSE values into 1's and 0's and produces a two-dimensional array of 4 columns and 3 rows (rows are separated by semicolons and each column of data by a comma):

`{0,0,0,0;0,0,0,0;0,1,0,0}`

The SUMPRODUCT functions multiplies the elements of the above array by the items of B2:E4 in the same positions:

`{0,0,0,0;0,0,0,0;0,1,0,0} * {22000,13800,8500,3500;25000,23000,22000,20000;700,2000,2300,2500}`

And because multiplying by zero gives zero, only the item corresponding to 1 in the first array survives:

`SUMPRODUCT({0,0,0,0;0,0,0,0;0,2000,0,0})`

Finally, SUMPRODUCT adds up the elements of the resulting array and returns a value of 2000.

One more amazingly simple way to do a matrix lookup in Excel is by using named ranges. Here's how:

**Part 1: Name columns and rows **

The fastest way to name each row and each column in your table is this:

- Select the whole table (A1:E4 in our case).
- On the
*Formulas*tab, in the*Defined Names*group, click**Create from Selection**or press the Ctrl + Shift + F3 shortcut. - In the
*Create Names from Selection*dialog box, select**Top row**and**Left column,**and click OK.

This automatically creates names based on the row and column headers. However, there are a couple of caveats:

- If your column and/or rows headers are numbers or contain specific characters that are not allowed in Excel names, the names for such columns and rows won't be created. To see a list of created names, open the Name Manager (Ctrl + F3). If some names are missing, define them manually as explained in How to name a range in Excel.
- If some of your row or column headers contain spaces, the spaces will be replaced with underscores, for example,
*Polar_bear*.

For our sample table, Excel automatically created only the row names. The column names have to be created manually because the column headers are numbers. To overcome this, you can simply preface the numbers with underscores, like *_1990*.

As the result, we have the following named ranges:

**Part 2: Make a matrix lookup formula **

To pull a value at the intersection of a given row and column, just type one of the following generic formulas in an empty cell:

=*row_name* *column_name*

Or vice versa:

=*column_name* *row_name*

For example, to get the population of blue whales in 1990, the formula is as simple as:

`=Blue_whale _1990`

If someone needs more detailed instructions, the following steps will walk you through the process:

- In a cell where you want the result to appear, type the equality sign (=).
- Start typing the name of the target row, say,
*Blue_whale*. After you've typed a couple of characters, Excel will display all existing names that match your input. Double-click the desired name to enter it in your formula:

- After the row name, type a
**space**, which works as the**intersection operator**in this case. - Enter the target column name (
*_1990*in our case).

- As soon as both the row and column names are entered, Excel will highlight the corresponding row and column in your table, and you press Enter to complete the formula:

Your matrix lookup is done, and the below screenshot shows the result:

That's how to look up in rows and columns in Excel. I thank you for reading and hope to see you on our blog next week!

2-dimensional lookup sample workbook

Excel formulas
CSV
Excel functions
Print
Financial functions
Vlookup
Merge data in Excel
SumIf
CountIf
Excel Compare
If statement
Excel Charts
Pivot Table
Conditional formatting
Excel formatting
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Shared email templates

Category: Excel Tips

60+ professional tools for Excel

2019–2010 to do your daily work

2019–2010 to do your daily work

Incredible product, even better tech support…AbleBits totally delivers!

Deborah Bryant

Anyone who works with Excel is sure to find their work made easier

Jackie Lee

The best spent money on software I've ever spent!

Patrick Raugh

Ablebits is a fantastic product - easy to use and so efficient.

Debra Celmer

Excel is at its best now

Annie C.

I don't know how to thank you enough for your Excel add-ins

Jennifer Morningstar

Anybody who experiences it, is bound to love it!

Kumar Nepa

AbleBits suite has really helped me when I was in a crunch!

Nelda Fink

I have enjoyed every bit of it and time am using it

Christian Onyekachi Nwosu

It's the best $100 we've ever spent!

Mike Cavanagh

I love the program, and I can't imagine using Excel without it!

Robert Madsen

One word… WOW!

Dave Brown

Love the products!

David Johnston

It is like having an expert at my shoulder helping me…

Linda Shakespeare

Your software really helps make my job easier

Jeannie C.

Thanks for a terrific product that is worth every single cent!

Dianne Young

I love your product

Brad Gibson

Awesome!!!

Sheila Blanchard

## 14 responses to "Excel INDEX MATCH MATCH and other formulas for two-way lookup"

Please also explain Xlookup and Xmatch which seem to replace not only vlookup/hlookup but also Index and Match

Hi Malcolm,

You are absolutely right. I've added the XLOOKUP formula to this tutorial. Also, we have a separate article that demonstrates all the abilities of this new function with formula examples: XLOOKUP function in Excel for vertical and horizontal lookup

hi need help with this

with the assigned value like a=1 , s=2, d=3, f=4

then fads=4132

tnx,

Can you please advise how we can drag the results down to apply the formulae to multiple rows in a sheet.

I tried double clicking the square and also the copy pste special formulae in mass but does not seem to update the value until we double click in the formula itself.

Thank you,

Nikhil

Hello Nikhil!

I recommend instructions on how to copy a formula in Excel

Thank you! I’ve looking for the index match match. Beautifully and easily put together.

Hi Svetlana,

How can I find row title based on the matrix value. In you Example I want to find row title for D3 (22,000) or E4 (2500). I need a reverse formula. It means I have the year and population but need to know where they belong to, Tiger, Polar bear,..

How can I solve it.

Ali

Thanks for this it was really helpful

A B C D E

A 34 34 34 34 34

B 54 54 54 54 54

C 12 11 10 9 8

D 7 6 5 4 3

E 2 1 15 8 7

I have this table, I want the value from this table when I enter AC it will give me 34. if there is any formula please let me know.

Hello!

I kindly ask you to have a closer look at the following paragraph of the article above.

If there is anything else I can help you with, please let me know.

Hi,

You've been the best Excel resource ive found!

Thanks for all your help!

Hope you can help this time too.

First time writing.

This is one of the formulas I have on top of my checking acct. registrar:

F13=

=SUMIF(INDEX(D33:D1000,MATCH(F10,A33:A1000,1),):INDEX(D33:D1000,MATCH(F12,A33:A1000,1),),F4,IF(F2="Dept",INDEX(A33:D1000,MATCH(F10,A33:A1000,1),2):INDEX(A33:D1000,MATCH(F12,A33:A1000,1),2),IF(F2="Credit",C33:C1000,0)))

Columns:

Date=A

Dept=B

Credit=C

Description=D

Running Balance=E

Inputs: (F2,F4,F10,F12)

F2= Column Heading (Dept,Credit)

F4= A repetitive Description entry ("Electric Bill","Income")

Date Range= Start date=F10, End date=F12

Output:F13

Data Rows= @ 33:1000

The dates are ascending in Column A, but because the dates aren't daily sequential, the formula results in using a date before(< F10) Because of the MATCH TYPE Rules!

When F10 Date exist the formula works fine, its only when F10 date doesn't exist the formula uses a date prior to that of F10! (MATCH TYPE Rules)

Do you know how I can correct the formula so that if F10 date doesn't exist, the formula will start evaluating the next available match/Date in Column A after F10?

Thanks!

Joe

Hello!

Unfortunately, without seeing your data it is difficult to give you any advice.

When using parameter 1 in the MATCH function, the data must be sorted. About your phrase "The dates are ascending in Column A, but because the dates aren't daily sequential" I cannot say that.

I can also suggest using a search by date range from F10 to F12. Pay attention to this article - INDEX MATCH with several criteria.

If there is anything else I can help you with, please let me know.

This is a great summary of all available methods for 2d lookups. Which method is the fastest for exact lookups?

Hello!

Here is the article that may be helpful to you: VLOOKUP in Excel - which formula is fastest?