The INDEX
and MATCH
functions in Excel are powerful tools for looking up and retrieving data. Unlike VLOOKUP
or HLOOKUP
, INDEX
and MATCH
offer more flexibility, especially when working with non-standard data layouts. Below are 20 examples of how to use INDEX
and MATCH
together, including sample data and formulas.
Basic Syntax
INDEX(array, row_num, [column_num])
: Returns a value from a specific row and column in a range.MATCH(lookup_value, lookup_array, [match_type])
: Returns the relative position of a value in a range.
Examples with Data
1. Basic Lookup
Task: Find the price of a product using its ID.
Product ID | Product Name | Price |
---|---|---|
A123 | Apple | 1.20 |
B456 | Banana | 0.50 |
C789 | Cherry | 2.00 |
Formula:
=INDEX(C2:C4, MATCH("B456", A2:A4, 0))
Result: 0.50
(the price of Banana).
2. Lookup with Multiple Columns
Task: Find the product name using its ID.
Product ID | Product Name | Price |
---|---|---|
A123 | Apple | 1.20 |
B456 | Banana | 0.50 |
C789 | Cherry | 2.00 |
Formula:
=INDEX(B2:B4, MATCH("C789", A2:A4, 0))
Result: Cherry
.
3. Lookup with Multiple Criteria
Task: Find the price of a product in a specific category.
Category | Product Name | Price |
---|---|---|
Fruit | Apple | 1.20 |
Fruit | Banana | 0.50 |
Vegetable | Carrot | 0.80 |
Formula:
=INDEX(C2:C4, MATCH(1, (A2:A4="Fruit")*(B2:B4="Banana"), 0))
Result: 0.50
(the price of Banana in the Fruit category).
4. Lookup with Wildcard
Task: Find the price of a product whose name starts with "App".
Product ID | Product Name | Price |
---|---|---|
A123 | Apple | 1.20 |
B456 | Banana | 0.50 |
C789 | Cherry | 2.00 |
Formula:
=INDEX(C2:C4, MATCH("App*", B2:B4, 0))
Result: 1.20
(the price of Apple).
5. Lookup with Approximate Match
Task: Find the discount percentage for a given purchase amount.
Purchase Amount | Discount % |
---|---|
0 | 0% |
100 | 5% |
500 | 10% |
1000 | 15% |
Formula:
=INDEX(B2:B5, MATCH(750, A2:A5, 1))
Result: 10%
(the closest match less than or equal to 750).
6. Lookup with Dates
Task: Find the sales amount for a specific date.
Date | Sales Amount |
---|---|
2023-10-01 | 500 |
2023-10-02 | 700 |
2023-10-03 | 600 |
Formula:
=INDEX(B2:B4, MATCH(DATE(2023,10,2), A2:A4, 0))
Result: 700
(the sales amount for October 2, 2023).
7. Lookup with Horizontal Data
Task: Find the price of a product in a horizontal table.
Product ID | A123 | B456 | C789 |
---|---|---|---|
Price | 1.20 | 0.50 | 2.00 |
Formula:
=INDEX(B2:D2, MATCH("B456", B1:D1, 0))
Result: 0.50
.
8. Lookup with Two-Way Lookup
Task: Find the sales amount for a specific product on a specific date.
Date | Apple | Banana | Cherry |
---|---|---|---|
2023-10-01 | 500 | 300 | 200 |
2023-10-02 | 700 | 400 | 100 |
Formula:
=INDEX(B2:D3, MATCH(DATE(2023,10,2), A2:A3, 0), MATCH("Banana", B1:D1, 0))
Result: 400
(the sales amount for Banana on October 2, 2023).
9. Lookup with Dynamic Range
Task: Find the price of a product using a dynamic named range.
Product ID | Product Name | Price |
---|---|---|
A123 | Apple | 1.20 |
B456 | Banana | 0.50 |
C789 | Cherry | 2.00 |
Steps:
Create a named range (e.g.,
Products
) forA2:A4
.Use the formula:
=INDEX(C2:C4, MATCH("B456", Products, 0))
Result: 0.50
.
10. Lookup with Array Formula
Task: Find the price of multiple products at once.
Product ID | Product Name | Price |
---|---|---|
A123 | Apple | 1.20 |
B456 | Banana | 0.50 |
C789 | Cherry | 2.00 |
Formula:
=INDEX(C2:C4, MATCH({"A123","C789"}, A2:A4, 0))
Result: 1.20
and 2.00
.
11. Lookup with Error Handling
Task: Return "Not Found" if the product doesn’t exist.
Product ID | Product Name | Price |
---|---|---|
A123 | Apple | 1.20 |
B456 | Banana | 0.50 |
C789 | Cherry | 2.00 |
Formula:
=IFERROR(INDEX(C2:C4, MATCH("XYZ", A2:A4, 0)), "Not Found")
Result: Not Found
.
12. Lookup with Case-Insensitive Match
Task: Find the price of a product without case sensitivity.
Product ID | Product Name | Price |
---|---|---|
A123 | Apple | 1.20 |
B456 | Banana | 0.50 |
C789 | Cherry | 2.00 |
Formula:
=INDEX(C2:C4, MATCH("banana", B2:B4, 0))
Result: 0.50
.
13. Lookup with Multiple Return Columns
Task: Retrieve the product name and price for a given product ID.
Product ID | Product Name | Price |
---|---|---|
A123 | Apple | 1.20 |
B456 | Banana | 0.50 |
C789 | Cherry | 2.00 |
Formula:
=INDEX(B2:C4, MATCH("C789", A2:A4, 0), {1,2})
Result: Cherry
and 2.00
.
14. Lookup with Reverse Search
Task: Find the last occurrence of a product.
Product ID | Product Name | Price |
---|---|---|
A123 | Apple | 1.20 |
B456 | Banana | 0.50 |
B456 | Banana | 0.60 |
Formula:
=INDEX(C2:C4, MATCH(2, 1/(A2:A4="B456")))
Result: 0.60
(the last occurrence of Banana).
15. Lookup with Dynamic Array
Task: Return all prices for a specific category.
Category | Product Name | Price |
---|---|---|
Fruit | Apple | 1.20 |
Fruit | Banana | 0.50 |
Vegetable | Carrot | 0.80 |
Formula:
=FILTER(C2:C4, A2:A4="Fruit")
Result: 1.20
and 0.50
.
16. Lookup with Text Functions
Task: Find the price of a product using partial text.
Product ID | Product Name | Price |
---|---|---|
A123 | Apple | 1.20 |
B456 | Banana | 0.50 |
C789 | Cherry | 2.00 |
Formula:
=INDEX(C2:C4, MATCH("Ban*", B2:B4, 0))
Result: 0.50
.
17. Lookup with Logical Operators
Task: Find the first product with a price greater than 1.00.
Product ID | Product Name | Price |
---|---|---|
A123 | Apple | 1.20 |
B456 | Banana | 0.50 |
C789 | Cherry | 2.00 |
Formula:
=INDEX(B2:B4, MATCH(TRUE, C2:C4>1, 0))
Result: Apple
.
18. Lookup with Spill Range
Task: Return all details for a specific product ID.
Product ID | Product Name | Price | Category |
---|---|---|---|
A123 | Apple | 1.20 | Fruit |
B456 | Banana | 0.50 | Fruit |
C789 | Cherry | 2.00 | Fruit |
Formula:
=INDEX(B2:D4, MATCH("B456", A2:A4, 0), {1,2,3})
Result: Banana
, 0.50
, and Fruit
.
19. Lookup with Array Input
Task: Look up prices for multiple product IDs at once.
Product ID | Product Name | Price |
---|---|---|
A123 | Apple | 1.20 |
B456 | Banana | 0.50 |
C789 | Cherry | 2.00 |
Formula:
=INDEX(C2:C4, MATCH({"A123","C789"}, A2:A4, 0))
Result: 1.20
and 2.00
.
20. Lookup with Conditional Formatting
Task: Highlight cells where the price is greater than 1.00.
Product ID | Product Name | Price |
---|---|---|
A123 | Apple | 1.20 |
B456 | Banana | 0.50 |
C789 | Cherry | 2.00 |
Formula (for conditional formatting):
=INDEX(C2:C4, MATCH(A2, A2:A4, 0)) > 1
Result: Highlights Apple
and Cherry
.
These examples demonstrate the versatility of INDEX
and MATCH
in Excel.
No comments:
Post a Comment