## 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.