Combining INDIRECT with MATCH

Introduction to the INDIRECT Function

• The INDIRECT function is often used as a substitute for reference functions like HLOOKUP, VLOOKUP, OFFSET, INDEX, and CHOOSE.

Explanation of the INDIRECT Function

• Function Structure: The INDIRECT function takes a reference in quotation marks and outputs the value of that reference.

  • Example: For gross profit in 2016, to create a static INDIRECT formula to return the value 300, we would reference cell E8.

Dynamic Use of INDIRECT Function

• A static INDIRECT reference lacks practical use; thus, making it dynamic is essential.

• The goal is to replace the column and row references dynamically based on user selection.

  • Column Replacement: The column reference (like E) should dynamically correspond to the selected exit year.

  • Row Replacement: The row number (like 8) also needs to be dynamic and adaptable.

Understanding the Second Argument of INDIRECT Function

• A1 Style vs R1C1 Style:

  • The default (A1 style) references cells as "column letter and row number" (e.g., E8).

  • Setting the second argument to FALSE switches to R1C1 style, which references cells as "row number followed by column number" (e.g., R8C5 for E8).

  • This makes embedding a MATCH function easier to retrieve dynamic outputs.

Creating a Dynamic INDIRECT Formula

• Steps for Formula Creation:

  1. Set the second argument of INDIRECT to FALSE.

  2. Using the R1C1 style, replace the cell reference with R8C5 to return 300.

• To achieve dynamic cell references:

  • Use concatenation to build the reference string:

  • Concatenate the row number, use the MATCH function for the column lookup.

Utilizing the MATCH Function

• The MATCH function is utilized to dynamically identify the relative position of the lookup value.

  • MATCH Syntax: MATCH(lookup_value, lookup_array, match_type).

  • An exact match is typically needed (match_type = 0).

  • If starting at a specific row, account accordingly by adjusting the index.

• Example of integrating MATCH with INDIRECT:

  • Replace the row number (8) with a MATCH function output to make it dynamic.

Continuing the Dynamization Process

• The column reference (5 in R8C5) also needs to be made dynamic using a MATCH function relative to the metrics tracked (like gross profit).

• The same steps apply as for the row:

  • Ensure that the MATCH function accurately identifies the position within the corresponding array.

Evaluation and Adjustment

• Testing the formula by changing reference years reflects the correct dynamic response.

• Comparison with the CHOOSE function shows limitations, as CHOOSE cannot adapt dynamically in the same way as INDIRECT.

Best Practices and Recommendations

• While the INDIRECT function can handle dynamic referencing, it's recommended to prefer simpler alternatives when possible for clarity.

• Understanding the second condition of the INDIRECT function is key for handling complex references effectively.

Conclusion

• The INDIRECT function, when used with MATCH, provides a powerful method for dynamically referencing cells. However, it's important to recognize that multiple methods can achieve similar goals, and the choice should be based on the context of use and complexity.