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:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Item Code | Item Name | Qty | Unit Price | Unit Cost |
| 2 | 1/4/2019 | Item100 | Dell Laptop 1 | 2 | 1200 | 1000 |
| 3 | 1/4/2019 | Item200 | Dell Laptop 2 | 1 | 1240 | 1040 |
| 4 | 1/5/2019 | Item300 | Dell Laptop 3 | 3 | 1130 | 1090 |
| 5 | 1/5/2019 | Item400 | Dell Laptop 4 | 4 | 1130 | 1010 |
| 6 | 1/5/2019 | Item500 | Dell Laptop 5 | 5 | 1240 | 990 |
| 7 | 1/6/2019 | Item600 | Dell Laptop 6 | 1 | 1120 | 1050 |
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.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Item Code | Item Name | Qty | Unit Price | Unit Cost |
| 2 | 1/4/2019 | Item1000 | Dell Laptop Black 1 | 2 | 1200 | 1100 |
| 3 | 1/4/2019 | Item2000 | Dell Laptop Black 2 | 1 | 1240 | 1140 |
| 4 | 1/5/2019 | Item3000 | Dell Laptop Black 3 | 3 | 1130 | 1030 |
| 5 | 1/5/2019 | Item4000 | Dell Laptop Blue 1 | 4 | 1130 | 999 |
| 6 | 1/5/2019 | Item5000 | Dell Laptop Blue 2 | 5 | 1240 | 1180 |
| 7 | 1/6/2019 | Item6000 | Dell Laptop Blue 3 | 1 | 1120 | 1050 |
| 8 | 1/7/2019 | Item7000 | Dell Laptop Green 1 | 7 | 1150 | 1000 |
| 9 | 1/9/2019 | Item8000 | Dell Laptop Green 2 | 3 | 1100 | 1080 |
| 10 | 1/9/2019 | Item9000 | Dell Laptop Grey 1 | 8 | 1200 | 1120 |
| 11 | 1/10/2019 | Item1000 | Dell Laptop Grey 2 | 2 | 1000 | 950 |
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.