Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

Learn How to Perform a Two Dimensional Lookup in Excel

Excel allows a user to do a two-dimensional lookup using the INDEX and MATCH functions. The MATCH function returns a row and a column for values in a table, while the INDEX returns a value for row and column. This step by step tutorial will assist all levels of Excel users to learn how to perform a two-dimensional lookup in Excel.

Figure 1. The final result of the formula

Syntax of the INDEX formula

The generic formula for the INDEX function is:

=INDEX(array, row_num, column_num)

The parameters of the INDEX function are:

  • array – a range of cells where we want to get a data
  • row_num – a number of a row in the array for which we want to get a value
  • column_num – a column in the array which returns a value.

Syntax of the MATCH formula

The generic formula for the MATCH function is:

=MATCH(lookup_value, lookup_array, [match_type])

The parameters of the MATCH function are:

  • lookup_value – a value which we want to find in the lookup_array
  • lookup_array – the array where we want to find a value
  • [match_type] – a type of match. We put 0 as we want the exact match.

Setting up Our Data for the Formula

Our table is the matrix representing Sales per month and product. In column B, we have products and in the third row, we have months. In the range C4:G8, we have monthly sales per product. In the cells J2 and J3 we have the month and the product for which we want to get the Sales in J4.

Figure 2. Data that we will use in the example

Performing a Two-Dimensional Lookup in Excel

We want to get the Sales for April (J2) and Product B (J3) in the cell J4. This data has to be looked up in the table C4:G8.

The formula looks like:

=INDEX(C4:G8, MATCH(J3, B4:B8, 0), MATCH(J2, C3:G3, 0))

The first MATCH function has lookup_value J3, lookup_array B4:B8 and match_type 0. The result of this function is the row_num parameter of the INDEX function. The second MATCH function has lookup_value J2, lookup_array C3:G38 and match_type 0. The result of this function is the column_num parameter of the INDEX function. The array parameter is the range C4:G8.

To apply the formula, we need to follow these steps:

  • Select cell J4 and click on it
  • Insert the formula: =INDEX(C4:G8, MATCH(J3, B4:B8, 0), MATCH(J2, C3:G3, 0))
  • Press enter.

Figure 3. Using the formula to perform a two-dimensional lookup

The first MATCH function returns 2, as “Product B” is in the second place in array B4:B8. The second MATCH returns 4, as “April” is in the 4 places in array C3:G3. Therefore, the INDEX function returns the value in the second row and fourth column from the range C4:G8. The result in J3 is $920.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Solution examples
index and match with duplicates. I need to use another column as a reference, so my return value has two match the value of two things for it to return
Solved by E. H. in 60 mins
Hello, I have a big spreadsheet in which I need to know how many patients came in each month based on provider. I am using the following formula but I still get the N/A error. =INDEX('No Show Appts Data'!D:E, MATCH(1, 'No Show Appts Data'!D:D='No Shows Data'!M2)*('No Shows Data'!E:E='No Shows Data'!N1),0)) No Show Appts Data is the name of the sheet where Column D is Month Year of date in question and Column E is the provider. No Shows Data is the sheet where I am making all the formula calculation where Column M is Month and Year and Column N is the provider in question and therefore M2 is the month in question and N1 the provider in question. How do I fix this error? Thanks
Solved by F. H. in 40 mins
I need to find an INDEX function that will convert the Call Day (a number 1-7) to the actual weekday found in row 1 of the DayofWeek named range. I don't know if I'm supposed to use the MATCH function as well or not.
Solved by D. D. in 11 mins
I have two columns with names and a third one with email addresses. For every name that matches, I need to copy the email address to an empty column next to the matching name. .
Solved by E. U. in 15 mins
help with INDEX(IndexArea,MATCH($M$5,MatchRows,0),MATCH($M$6,MatchColumns,0))
Solved by Z. Y. in 12 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc