INDEX+MATCH Combination

The table provided explains how to use INDEX and MATCH functions to retrieve specific values based on a selected Product ID.

Here’s a breakdown of the setup:

Table Layout

• Columns: Product ID, Product Sales Value, Product Category, Product Description, and Sales Person.
• User Input: The user selects a Product ID (e.g., 7 in cell H8).
• Goal: Display the corresponding data (Sales Value, Category, Description, and Sales Person) in separate cells based on the selected Product ID.

Explanation of the Formulas

Each formula retrieves data from a specific column of the table using a combination of INDEX and MATCH.

1. Formula for Product Sales Value (I8)

=INDEX($F$8:$F$14;MATCH(H8;$A$8:$A$14;0))

• $F$8:$F$14: The range containing the Product Sales Value.
• MATCH(H8;$A$8:$A$14;0):
  • H8 contains the selected Product ID (e.g., 7).
  • $A$8:$A$14 is the range of Product IDs.
  • MATCH finds the row where H8 matches a value in $A$8:$A$14.
• INDEX uses the row number from MATCH to return the corresponding Product Sales Value from $F$8:$F$14.

2. Formula for Product Category (J8)

=INDEX($D$8:$D$14;MATCH(H8;$A$8:$A$14;0))

• $D$8:$D$14: The range containing the Product Category.
• MATCH works the same way, finding the row of the selected Product ID.
• INDEX retrieves the Product Category from $D$8:$D$14.

3. Formula for Product Description (K8)

=INDEX($B$8:$B$14;MATCH(H8;$A$8:$A$14;0))

• $B$8:$B$14: The range containing the Product Description.
• MATCH and INDEX operate as described above to retrieve the Product Description.

4. Formula for Sales Person (L8)

=INDEX($E$8:$E$14;MATCH(H8;$A$8:$A$14;0))

• $E$8:$E$14: The range containing the Sales Person.
• MATCH and INDEX work together to return the name of the Sales Person corresponding to the selected Product ID.

How It Works Step-by-Step

1. User Input: The user types or selects a Product ID (e.g., 7) in cell H8.
2. MATCH Function:
   • Searches $A$8:$A$14 for the value in H8 (7).
   • Returns the row number where the match is found (e.g., row 1 if 7 is the first value in $A$8:$A$14).
3. INDEX Function:
   • Uses the row number from MATCH to retrieve the corresponding value from the specified column.
   • For example, it retrieves:
     • Sales Value from $F$8:$F$14.
     • Product Category from $D$8:$D$14.
     • Product Description from $B$8:$B$14.
     • Sales Person from $E$8:$E$14.

Notes

• Dynamic Selection: The formulas dynamically adjust based on the Product ID entered in H8.
• Range Consistency: Ensure all ranges ($A$8:$A$14, $B$8:$B$14, etc.) cover the same rows to avoid errors.
• Error Handling: If the Product ID in H8 is not found, MATCH will return an error. Use IFERROR to handle it gracefully:

=IFERROR(INDEX($F$8:$F$14;MATCH(H8;$A$8:$A$14;0));”Not Found”)

This setup efficiently retrieves data without requiring manual lookup! Let me know if you need clarification or further assistance.