What it does
Toggles the active cell formula between three lookup functions in this order: VLOOKUP >> XLOOKUP >> INDEX/MATCH.  It looks for the first instance of one of those functions within the active cell formula, extracts the parameters, and converts it to the next one in the cycle.

When to use it
When you want to quickly update from one lookup function type to the next or learn new ones (most people just know of VLOOKUP, but it often has limitations compared to the others).

Why to use it
It’s a quick and easy way to toggle between the three different lookup formula types.

Default shortcut
None

Other Details

• Category: Formulas / General
• Difficulty: 2/5
• Usage/frequency: 4/5
• Automation factor: 4/5 (estimated 60 seconds saved each time used)
• Type: Shortcut
• Date added: 11/3/2023
• Tags: Formulas, toggle, lookup, VLOOKUP, XLOOKUP, INDEX, MATCH

Related Macros
Show Cell References

Other Articles
None

None

Prerequisites
Select the cell containing the lookup function type you’d like to change.  If the active cell does not contain a VLOOKUP, XLOOKUP, or INDEX/MATCH function(s), nothing will happen.

Instructions
With the desired cell range selected, run the Toggle Lookup macro.  The first function among VLOOKUP, XLOOKUP, and INDEX/MATCH will be replaced with the next one in the cycle leveraging the parameters within the function.  Run again to cycle to the next one and so forth.  See the animated screenshot of this in action below (focus on the formula bar).

None