INDEX and MATCH Excel with Examples


How to Use INDEX and MATCH with Examples

In this tutorial, you’ll learn how to use the combination of INDEX() and MATCH() to lookup a value and retrieve some other related information.

First, learn INDEX() and MATCH(), and second, learn to use the combination of INDEX() and MATCH().

INDEX

INDEX() returns a value or a reference to specified cells from within a range or table.

The syntax of INDEX() is as follows:

=INDEX(array, row_number, [column_number])

Demo data:

 ABCDEF
1DateItem CodeItem NameQtyUnit PriceUnit Cost
21/4/2019Item100Dell Laptop 1212001000
31/4/2019Item200Dell Laptop 2112401040
41/5/2019Item300Dell Laptop 3311301090
51/5/2019Item400Dell Laptop 4411301010
61/5/2019Item500Dell Laptop 551240990
71/6/2019Item600Dell Laptop 6111201050

The following example returns the value at the intersection of the 2nd row and the 3rd column in the range A2:F7:

=INDEX(A2:F7,2,3)

The above formula returns Dell Laptop 2.

Here is another example:

=INDEX(C2:C7,2)

INDEX() locates the 3rd row in the range C2: C7 and returns Dell Laptop 3

Here we omitted column_number because the range contains only one column. 

Note that when a range contains multiple rows and columns, you need to supply both row_number and column_number.

MATCH

MATCH() searches for the specified value and then returns the position of that value.

The syntax of MATCH() is as follows:

=MATCH(lookup_value, lookup_array, [match_type])

Searching for an exact match

To perform an exact match, set match_type to 0.

The following example returns the position of Dell Laptop 2:

=MATCH("Dell laptop 2",C2:C7,0)

The above formula returns 2.

Note that MATCH() is not case-sensitive.

Search for an Approximate Match

To perform an approximate match, set match_type to 1 or -1.

If you set match_type to 1, MATCH() searches for the largest value that is less than or equal to lookup_value.

If you set match_type to -1, MATCH() searches for the smallest value that is greater than or equal to lookup_value.

Note that if you set match_type to 1, you must sort values in lookup_array in ascending order. 

And if you set match_type to -1, you must sort values in lookup_array in descending order.

The following example searches for the largest number that is less than or equal to 155:

=MATCH(155, {100,110,120,130,140,150,160,170},1)

The formula returns 6, which is the position of 150 in the array.

The following example searches for the smallest character that is greater than or equal to C:

=MATCH("C",{"h","g","f","e","d","b","a"},-1)

The formula returns 5, which is the position of d in the array.

INDEX and MATCH

Now that you have learned INDEX and MATCH. This section describes how to use the combination of INDEX() and MATCH().

Assume that you have the following demo data, and you want to search for Dell Laptop Blue 1.

 ABCDEF
1DateItem CodeItem NameQtyUnit PriceUnit Cost
21/4/2019Item1000Dell Laptop Black 1212001100
31/4/2019Item2000Dell Laptop Black 2112401140
41/5/2019Item3000Dell Laptop Black 3311301030
51/5/2019Item4000Dell Laptop Blue 141130999
61/5/2019Item5000Dell Laptop Blue 2512401180
71/6/2019Item6000Dell Laptop Blue 3111201050
81/7/2019Item7000Dell Laptop Green 1711501000
91/9/2019Item8000Dell Laptop Green 2311001080
101/9/2019Item9000Dell Laptop Grey 1812001120
111/10/2019Item1000Dell Laptop Grey 221000950

First, you need to get the position of Dell Laptop Blue 1 by using the MATCH function, as the following example shows:

=MATCH("Dell laptop blue 1",C2:C11,0)

Second, you place the above formula inside the INDEX() function, as the following example shows:

=INDEX(A2:F11,MATCH("Dell laptop blue 1",C2:C11,0),3)

If lookup_value is found, you can retrieve other related information such as Qty, unit price, or unit cost.

The following example returns the unit price of Dell Laptop Blue 1:

=INDEX(A2:F11,MATCH("Dell laptop blue 1",C2:C11,0),5)

The above formula returns 1130.

The formula to get the unit cost is as follows:

=INDEX(A2:F11,MATCH("Dell laptop blue 1",C2:C11,0),6)

The formula returns 999.

In this tutorial, you’ve learned how to use INDEX, MATCH, and the combination of INDEX and MATCH

You can use INDEX and MATCH to search for a value in a range of cells and retrieve other related information.

The combination of INDEX() and MATCH() and VLOOKUP() can do the same things.

Leave a Reply

Your email address will not be published. Required fields are marked *