ADVANCED LOGIC
INDEX and MATCH
The INDEX and MATCH functions work together in Excel to perform flexible and powerful lookups. Here’s a quick breakdown:
INDEX:
This function retrieves a value from a specific location (row and column) within a range you provide.
MATCH:
This function finds the relative position (row number) of a specific value within a range based on your search criteria.
The Teamwork:
- MATCH searches for your lookup value in a designated range.
- MATCH returns the row number where the lookup value is found.
- INDEX uses this row number to pinpoint the exact cell within the range.
- INDEX extracts the value from that cell and returns it as the result.
Benefits of INDEX and MATCH:
- Flexibility: Look up data based on various criteria (not just the first column like VLOOKUP).
- Dynamic: Use cell references for lookup values and ranges, making formulas adaptable.
- Two-way Lookups: Find both row and column positions for complex lookups.
Example:
Imagine you have a table with employee names (column A), IDs (column B), and salaries (column C). You want to find the salary of an employee whose ID is in cell D2.
Here’s the formula:
Excel
=INDEX($C$2:$C$10, MATCH(D2, $B$2:$B$10, 0))
- $C$2:$C$10: Salary range (absolute reference to avoid changing when copied).
MATCH(D2, $B$2:$B$10, 0): Finds the row number of the ID in cell D2 within the ID range (exact match with 0).
Remember:
- Enter the INDEX MATCH formula with Ctrl+Shift+Enter (for array formula).
- Adjust cell references based on your data location.
Further Exploration:
For more advanced usage and examples, you can search online for “INDEX MATCH multiple criteria” or “INDEX MATCH with wildcards” to explore its full potential.
UNIQUE
The UNIQUE function in Excel is a handy tool to retrieve a list of unique values from your data. Here’s a quick guide:
Function:
=UNIQUE(array)
Purpose:
Returns a list containing only the distinct values from the specified range (array).
Key Points:
- Dynamic Arrays: UNIQUE is a dynamic array function, meaning it spills the results into neighboring cells automatically.
- Multiple Data Types: Works with text, numbers, dates, and other data types.
- Optional Argument: You can include a second argument (TRUE/FALSE) to control how duplicates are handled:
- TRUE (or omitted): Returns all unique values, including the first occurrence of duplicates. (Default behavior)
- FALSE: Returns only values that appear once in the data.
Benefits:
- Simple and Efficient: Easy to use for quick identification of unique items.
Example:
Let’s say you have a list of product codes (column A) with some duplicates. You want to find all the unique product codes.
Here’s the formula:
Excel=
UNIQUE(A1:A15)
This will extract a list of all unique product codes from A1 to A15, even if some codes appear multiple times.
Additional Notes:
- Ensure there are enough empty cells below the formula to accommodate the unique values.
- For sorted results, combine UNIQUE with SORT: =SORT(UNIQUE(A1:A15)).
- Explore online resources for in-depth tutorials and examples on using UNIQUE with complex data sets.
