Go Back

Tips on Performing an INDEX and MATCH with Two Criteria

Excel allows a user to do a lookup with two criteria using the INDEX and MATCH functions. The MATCH function returns a row for a value in a table, while the INDEX returns a value for that row. This step by step tutorial will assist all levels of Excel users in learning tips on performing an INDEX and MATCH with two criteria.

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 consists of 4 columns: “Delivery Number” (column B), “Product ID” (column C), “Delivery Date” (column D) and “Amount” (column E).  In the cell H5, we want to get the amount from the table, where the Delivery Number is equal to H2, the Product ID is equal to H3 and Delivery Date is equal to H4.

Figure 2. Data that we will use in the example

Performing an INDEX and MATCH with Two Criteria

We want to get an amount, from the lookup table B3:E9, where the Delivery number is 1003, the Product ID is 105 and the Delivery date is 1/12/2019.

The formula looks like:

{=INDEX(E3:E9, MATCH(1, (H2=B3:B9) * (H3=C3:C9) * (H4=D3:D9), 0))}

The lookup_value parameter of the MATCH function is 1. The lookup_array is (H2=B3:B9) * (H3=C3:C9) * (H4=D3:D9). The cells H2, H3 and H4 are compared to the arrays and as a result arrays with 0 and 1 is returned. The match_type is 0, which means the approximate match. The result of the MATCH function is the row_num parameter of the INDEX function. The array is the range E3:E9.

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

  • Select cell H5 and click on it
  • Insert the formula: =INDEX(E3:E9,MATCH(1,(H2=B3:B9)*(H3=C3:C9)*(H4=D3:D9),0))
  • Press ctrl, shift and enter simultaneously to convert the formula in the array function.

When we evaluate all three comparations in the MATCH function, the lookup_array looks like in Figure 3.

Figure 3. Evaluate the formula

Therefore, the MATCH function returns 3.

Figure 4. Using the INDEX and MATCH Formula

The third row is the row_num parameter of the INDEX function. Finally, the amount from the third row of the lookup_table is the result of the function. The result in the cell H5 is $700.

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.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:

Leave a Comment

avatar