Introduction
Welcome to this tutorial on INDEX-MATCH, a powerful combination in Excel for looking up and retrieving data from tables. In this tutorial, I'll cover the basic concepts, syntax, examples, and practical applications of INDEX-MATCH.
Understanding INDEX-MATCH in Excel
The INDEX-MATCH combination in Excel is a powerful tool for looking up and retrieving data from tables. It is often used as an alternative to VLOOKUP or HLOOKUP.
Syntax:
The basic syntax for INDEX-MATCH is as follows:
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))- return_range:
- The range from which you want to retrieve the value.
- lookup_value:
- The value you want to search for within the lookup_range.
- lookup_range:
- The range where Excel will search for the lookup_value.
- 0 (zero):
- Used for an exact match. If you want an approximate match, you can use 1.
Example:
Suppose you have a table with Student IDs in column A and corresponding Grades in column B. You want to find the grade for a specific Student ID using INDEX-MATCH. If the Student ID you are looking for is in cell C1, the formula would be:
=INDEX(B:B, MATCH(C1, A:A, 0))
This formula will return the grade for the specified Student ID.
Example 1: Basic INDEX-MATCH
Let's consider a simple dataset:
| A | B | |
|---|---|---|
| 1 | Student I'd | Grade |
| 2 | 101 | A |
| 3 | 102 | B |
| 4 | 103 | C |
To find the grade for StudentID 102:
=INDEX(B2:B4, MATCH(102, A2:A4, 0))
Example 2: Two-Dimensional Lookup
Suppose we have a sales table:
| A | B | c | |
|---|---|---|---|
| 1 | Product | Region | Sales |
| 2 | Apple | North | $500 |
| 3 | Banana | South | $300 |
| 4 | Orange | East | $700 |
To find the sales for "Banana" in the "South" region:
=INDEX(C2:C4, MATCH("Banana", A2:A4, 0)) // Returns $300
Example 3: SUM with INDEX-MATCH
Consider a dataset of expenses:
| A | B | |
|---|---|---|
| 1 | Category | Amount |
| 2 | Office Supplies | $150 |
| 3 | Travel | $300 |
| 4 | Meals | $200 |
To calculate the total expenses for "Travel":
=SUM(INDEX(B2:B4, MATCH("Travel", A2:A4, 0)))
Example 4: COUNT with INDEX-MATCH
Consider a dataset of products and their quantities:
| A | B | |
|---|---|---|
| 1 | Product | Quantity |
| 2 | Chair | 5 |
| 3 | Desk | 3 |
| 4 | Lamp | 8 |
To count the number of "Lamps":
=COUNT(INDEX(B2:B4, MATCH("Lamp", A2:A4, 0)))
Example 5: MAX with INDEX-MATCH
Consider a dataset of temperatures:
| A | B | |
|---|---|---|
| 1 | City | Temperature (°C) |
| 2 | New York | 28 |
| 3 | London | 21 |
| 4 | Tokyo | 32 |
To find the maximum temperature in "Tokyo":
=MAX(INDEX(B2:B4, MATCH("Tokyo", A2:A4, 0)))
Example 6: MIN with INDEX-MATCH
Consider a dataset of test scores:
| A | B | |
|---|---|---|
| 1 | Student | Score |
| 2 | Alice | 90 |
| 3 | Bob | 85 |
| 4 | Charlie | 95 |
To find the minimum score for "Bob":
=MIN(INDEX(B2:B4, MATCH("Bob", A2:A4, 0)))
Example 7: AVERAGE with INDEX-MATCH
Consider a dataset of product ratings:
| A | B | |
|---|---|---|
| 1 | Product | Rating |
| 2 | Laptop | 4.5 |
| 3 | Smartphone | 4.0 |
| 4 | Headphones | 3.8 |
To calculate the average rating for "Smartphone":
=AVERAGE(INDEX(B2:B4, MATCH("Smartphone", A2:A4, 0)))
Example 8: IF with INDEX-MATCH
Consider a dataset of exam scores:
| A | B | |
|---|---|---|
| 1 | Student | Score |
| 2 | Alice | 90 |
| 3 | Bob | 85 |
| 4 | Charlie | 95 |
To assign a grade of "Pass" or "Fail" based on the score for "Bob":
=IF(INDEX(B2:B4, MATCH("Bob", A2:A4, 0)) >= 90, "Pass", "Fail")
Example 9: IFS with INDEX-MATCH
Consider a dataset of product ratings:
| A | B | |
|---|---|---|
| 1 | Product | Rating |
| 2 | Laptop | 4.5 |
| 3 | Smartphone | 4.0 |
| 4 | Headphones | 3.8 |
To categorize the product as "High," "Medium," or "Low" based on the rating for "Laptop":
=IFS(INDEX(B2:B4, MATCH("Laptop", A2:A4, 0)) >= 4.5, "High", INDEX(B2:B4, MATCH("Laptop", A2:A4, 0)) >= 4.0, "Medium", INDEX(B2:B4, MATCH("Laptop", A2:A4, 0)) < 4.0, "Low")
Example 10: SUMIF with INDEX-MATCH
Consider a dataset of sales:
| A | B | C | |
|---|---|---|---|
| 1 | Product | Region | Sales |
| 2 | Apple | North | $500 |
| 3 | Banana | South | $300 |
| 4 | Orange | East | $700 |
To calculate the total sales for "North" region:
=SUMIF(INDEX(B2:B4, MATCH("North", A2:A4, 0)), "North", C2:C4)
Example 11: COUNTIF with INDEX-MATCH
Consider a dataset of products and their quantities:
| A | B | |
|---|---|---|
| 1 | Product | Quantity |
| 2 | Chair | 5 |
| 3 | Desk | 3 |
| 4 | Lamp | 8 |
To count the number of products with a quantity greater than 5:
=COUNTIF(INDEX(B2:B4, MATCH(5, A2:A4, 0)), ">5")
Example 12: MAXIFS with INDEX-MATCH
Consider a dataset of temperatures:
| A | B | |
|---|---|---|
| 1 | City | Temperature (°C). |
| 2 | New York | 28 |
| 3 | London | 21 |
| 4 | Tokyo | 32 |
To find the maximum temperature for cities with a temperature above 25°C:
=MAXIFS(INDEX(B2:B4, MATCH(25, A2:A4, 0)), A2:A4, ">25")
Example 13: MINIFS with INDEX-MATCH
Consider a dataset of test scores:
| A | B | |
|---|---|---|
| 1 | Student | Score |
| 2 | Alice | 90 |
| 3 | Bob | 85 |
| 4 | Charlie | 95 |
To find the minimum score for students with a score above 80:
=MINIFS(INDEX(B2:B4, MATCH(80, A2:A4, 0)), A2:A4, ">80")
Example 14: AVERAGEIF with INDEX-MATCH
Consider a dataset of product ratings:
| A | B | |
|---|---|---|
| 1 | Product | Rating |
| 2 | Laptop | 4.5 |
| 3 | Smartphone | 4.0 |
| 4 | Headphones | 3.8 |
To calculate the average rating for products with a rating below 4.0:
=AVERAGEIF(INDEX(B2:B4, MATCH(4.0, A2:A4, 0)), "<4.0")
To calculate the average rating for "Smartphone":
=AVERAGE(INDEX(B2:B4, MATCH("Smartphone", A2:A4, 0)))
Example 15: RANK with INDEX-MATCH
Consider a dataset of exam scores:
| A | B | |
|---|---|---|
| 1 | Student | Score |
| 2 | Alice | 90 |
| 3 | Bob | 85 |
| 4 | Charlie | 95 |
To rank students based on their scores:
=RANK(INDEX(B2:B4, MATCH("Bob", A2:A4, 0)), B2:B4)
