Monday, 27 January 2025

index and match 20 examples

 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 IDProduct NamePrice
A123Apple1.20
B456Banana0.50
C789Cherry2.00

Formula:

excel
Copy
=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 IDProduct NamePrice
A123Apple1.20
B456Banana0.50
C789Cherry2.00

Formula:

excel
Copy
=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.

CategoryProduct NamePrice
FruitApple1.20
FruitBanana0.50
VegetableCarrot0.80

Formula:

excel
Copy
=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 IDProduct NamePrice
A123Apple1.20
B456Banana0.50
C789Cherry2.00

Formula:

excel
Copy
=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 AmountDiscount %
00%
1005%
50010%
100015%

Formula:

excel
Copy
=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.

DateSales Amount
2023-10-01500
2023-10-02700
2023-10-03600

Formula:

excel
Copy
=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 IDA123B456C789
Price1.200.502.00

Formula:

excel
Copy
=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.

DateAppleBananaCherry
2023-10-01500300200
2023-10-02700400100

Formula:

excel
Copy
=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 IDProduct NamePrice
A123Apple1.20
B456Banana0.50
C789Cherry2.00

Steps:

  1. Create a named range (e.g., Products) for A2:A4.

  2. Use the formula:

excel
Copy
=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 IDProduct NamePrice
A123Apple1.20
B456Banana0.50
C789Cherry2.00

Formula:

excel
Copy
=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 IDProduct NamePrice
A123Apple1.20
B456Banana0.50
C789Cherry2.00

Formula:

excel
Copy
=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 IDProduct NamePrice
A123Apple1.20
B456Banana0.50
C789Cherry2.00

Formula:

excel
Copy
=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 IDProduct NamePrice
A123Apple1.20
B456Banana0.50
C789Cherry2.00

Formula:

excel
Copy
=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 IDProduct NamePrice
A123Apple1.20
B456Banana0.50
B456Banana0.60

Formula:

excel
Copy
=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.

CategoryProduct NamePrice
FruitApple1.20
FruitBanana0.50
VegetableCarrot0.80

Formula:

excel
Copy
=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 IDProduct NamePrice
A123Apple1.20
B456Banana0.50
C789Cherry2.00

Formula:

excel
Copy
=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 IDProduct NamePrice
A123Apple1.20
B456Banana0.50
C789Cherry2.00

Formula:

excel
Copy
=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 IDProduct NamePriceCategory
A123Apple1.20Fruit
B456Banana0.50Fruit
C789Cherry2.00Fruit

Formula:

excel
Copy
=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 IDProduct NamePrice
A123Apple1.20
B456Banana0.50
C789Cherry2.00

Formula:

excel
Copy
=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 IDProduct NamePrice
A123Apple1.20
B456Banana0.50
C789Cherry2.00

Formula (for conditional formatting):

excel
Copy
=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. 

on

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)