Basic INDEX MATCH exact

While working with Excel, we are able to retrieve data from a dataset based on an exact match by using INDEX and MATCH functions.  The INDEX function returns a value as specified from within a range, while the MATCH function returns the position of a value in a range.

This step by step tutorial will assist all levels of Excel users in retrieving information based on an exact match.  

Figure 1. Final result: Using Basic INDEX and exact MATCH

Final formula: =(INDEX($B$3:$E$7,MATCH(H$2,$B$3:$B$7,0),2))

Syntax of the INDEX function

=INDEX(array, row_num, column_num)

The parameters are:

• array – a range of cells where we want to retrieve some data
• row_num – the row in the array from which we want to retrieve data
• column_num – the column in the array from which we want to retrieve data; if the array has only one column, column_num can be omitted

Syntax of the MATCH function

=MATCH(lookup_value, lookup_array, [match_type])

The parameters are:

• lookup_value – a value which we want to find in the lookup_array
• lookup_array – the range of cells containing the value we want to match
• [match_type] – optional; the type of match; if omitted, the default value is 1; We use 0 to find an exact match

Setting up Our Data

Our table contains a list of Item ID (column B), Color (column C), Size (column D) and Orders (column E). In cell H2, we enter “10003”, which is the  Item ID that we want to match. In cells H3:H5, we want to retrieve the data for Color, Size and Orders of Item ID 10003.

Figure 2. Sample data to retrieve specific data based on exact match

Retrieve data using INDEX and MATCH

In order to retrieve the data for Color, Size and Orders of Item ID 10003, we follow these steps:

Step 1.  Select cell H3

Step 2.  Enter the formula: =(INDEX($B$3:$E$7,MATCH(H$2,$B$3:$B$7,0),2))

Step 3: Press ENTER

Step 4: Copy the formula in cell H3 to cells H4:H5 by clicking the “+” icon at the bottom right-corner of cell H3 and dragging it down

The dollar symbols “$” in the formula fix the cells so that we can easily copy and paste the formula to other cells.

Figure 3. Entering the formula to retrieve the matching color of item 10003

Our array for the INDEX function is the range B3:E7 which contains our data.  The row number is determined through the MATCH function, which finds the exact match and returns the position of cell H2, or Item ID “10003” in the range B3:B7.  The column number is 2, because the column “Color” is the second column in our array.

As a result, our formula returns “Red”in cell H3, which is the color of Item 10003.  

Cells H4 and H5 return the corresponding size and orders for Item 10003, as shown below.  

Figure 4. Output: Using INDEX and MATCH to retrieve data based on exact match

INDEX formula with only one column

It is also possible to work with the INDEX formula using only one column.  We can rewrite the formula and use the needed range as the array, so that there is no need to supply the INDEX formula with a column number.  

In column C11 below, we want to retrieve the data for color of Item 10003.  In C11, we enter the formula =(INDEX(C3:C7,MATCH(C$9,$B$3:$B$7,0))).  Note that the array used “C3:C7” is only in column C.  

The result is “Red”, which is the same as in the previous example.  

Figure 5. Comparison: INDEX formula using multiple or single column

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.