Lesson 156: How to use Index and Match in Excel

Instead of using the VLOOKUP function, you can also use INDEX and MATCH in Excel.

Join the channel Telegram of the AnonyViet 👉 Link 👈

How to use Index and Match in Excel

Match

The MATCH function returns the position of a value within a given range. For example, the MATCH function below looks for the value 53 in the range B3:B9.

Explanation: 53 (first parameter) is found at position 5 in the range B3:B9 (second parameter). In this example, we use the MATCH function to return the correct result, so we set the third parameter to 0.

Index

The INDEX function below returns the value of the position you want.

Explanation: the INDEX function returns the 5th value (second parameter) in the range E3:E9 (first parameter).

Index and Match

Replace the value 5 in the INDEX function (see previous example) with the MATCH function (see first example) to look up the salary of ID 53.

Explanation: the MATCH function returns position 5. The INDEX function needs position 5. It’s a perfect match. If you want, you can also use the Vlookup function.

Two-way lookup

The INDEX function can also return a specific value in a two-way range. For example, use INDEX and MATCH in Excel to do a two-way lookup.

Case sensitive lookup

By default, the VLOOKUP function performs case-insensitive lookups. However, you can use INDEX, MATCH, and EXACT in Excel to perform case-sensitive lookups.

Note: MIA Reed’s exact salary lookup formula, not Mia Clark’s.

Search on the left

The Vlookup function only looks for the right side. Don’t worry, you can use INDEX and MATCH in Excel to do left-side detection.

Note: as we drag this formula down, the absolute references ($E$4:$E$7 and $G$4:$G$7) will stay the same, while the relative references (A2) change to A3, A4, A5, etc