## Perform a Two Dimensional Lookup in Excel

• Post author:
• Post category:Uncategorized

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.