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.

Leave a Comment


Subscribe to

Get updates on helpful Excel topics

Subscribe to
Trusted by people who work at, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc