MATCH Function & Combinations

Match Function Overview

  • The MATCH Function is an essential function in Excel that finds the relative position of an item in an array that matches a specified lookup value.

    • It may not seem useful on its own but becomes powerful when combined with other lookup and reference functions.

Syntax of the MATCH Function

  • The syntax of the MATCH function requires three arguments:

    • lookup_value: The value you want to find the relative position for.

    • Example used: Year (e.g. 2015).

    • lookup_array: The range of cells that contains the possible matches.

    • Example used: The array of years (2014 through 2018).

    • match_type: Specifies how Excel matches the lookup value with the values in the lookup array.

    • Commonly, the third argument is set to 0 for an exact match.

Features of MATCH Function

  • The function returns the position of the lookup value as its output:

    • Example: For an input of 2015, MATCH outputs 2 since 2015 is the second item in the array.

    • Additional outputs based on different inputs:

    • 2016 → 3

    • 2017 → 4

    • 2018 → 5

Using the MATCH Function in Excel

  • Practical application of the match function involves the following steps:

    1. Input a year as the lookup_value.

    2. Define the lookup_array consisting of the range of years.

    3. Set the match_type to require an exact match, thus ensuring the formula operates correctly.

  • Encouragement to practice implementing the MATCH function in Excel was included.

Combining MATCH with Other Functions

  • The intention to create a more dynamic and complex formula by combining MATCH with functions such as HLOOKUP, VLOOKUP, INDEX, and OFFSET.

  • Several exercises are introduced to strengthen understanding by creating matching functionalities for revenues and metrics.

HLOOKUP and MATCH Combination

  1. Construct an HLOOKUP function:

    • Requires a lookup value (e.g., Year) and table array (the dataset).

    • Needs rows to skip to reach specific metrics (e.g., how many rows below for revenue).

    • Initially hardcodes the number of rows.

    • Replace the hardcoded number of rows with a dynamic output from the MATCH function, ensuring it responds to user inputs.

    • Ensures that changing inputs (like the selected metric) will yield the correct values dynamically.

VLOOKUP and MATCH Combination

  1. Construct a VLOOKUP function:

    • Similar structure; uses a lookup value to identify metrics in a vertical array.

    • Hardcoded column numbers initially, but dynamic adjustments are made using MATCH to ensure the correct column number responds to user changes in the exit year.

OFFSET and MATCH Combination

  1. OFFSET function is similar in use:

    • Requires starting cell as reference and how many rows and columns to offset.

    • Must account for different counting logic in OFFSET as compared to lookup functions (starts counting from the reference cell).

    • Introduces complexity due to necessary adjustments in matches to account for offset logic.

    • Often combined with MATCH for dynamic references that respond to user selections.

INDEX and MATCH Combination

  1. INDEX function usage:

    • Facilitates retrieval of values in a specific row and column.

    • When combined with MATCH, it allows for flexible and precise data retrieval based on user inputs, achieving dynamic responses.

Choosing with MATCH

  1. The CHOOSE function provides a means to select from a list of values based on an index number.

    • While it can dynamically pick a metric based on a match, it is static for a range of years (i.e., cannot efficiently respond to multiple metrics unless hardcoded for each case).

    • CHOOSE might work well for specific, limited applications but lacks the robustness needed for broader datasets without extensive adjustments.

Practice and Application

  • Highlighting the importance of practicing these functions to understand their application and establish comfort in using them methodically in Excel.

  • Encouragement to experiment with the MATCH function and its combinations to understand flexibility and functionality better.