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