We are continuing our series of enterprise spreadsheet performance tips and tricks to help you get your unwieldy spreadsheet data back on track. Building on our last post that explained the Excel INDEX MATCH function, this second installment in our series dives into INDEX MATCH MATCH, which offers you a more powerful version of the formula to look up values across rows and columns simultaneously.
While most business users default to VLOOKUP to find values in a table searching by row, INDEX MATCH may be lesser known, but it's a better alternative for vertical lookups. INDEX MATCH MATCH is a further extension of that for full table lookups that allows you do vertical and horizontal lookups. Instead of just a vertical lookup, INDEX MATCH MATCH give you the power to do a matrix lookup, also known as a two-way lookup.
Adding an additional MATCH formula to the basic INDEX MATCH formula enhances it so that you can perform a matrix lookup. It is essentially the same formula as INDEX MATCH, except your column reference is now dynamic. Because the MATCH formula returns a value based on the relative position of your lookup, the MATCH formula makes your column reference dynamic. Therefore, even if you insert a column into your data set, your column reference will automatically update so your return the value stays the same. And because these formulas don’t reference the entire data set to perform the lookup calculation, they require less processing power from Excel, speeding up your productivity.
View our step-by-step video guide on how to use this robust and maintainable way to dynamically find values in a table:
Do you have a tricky data management problem or a spreadsheet performance issue? Send your questions – we’d love to help.