Jeff Kayzerman

Enterprise Spreadsheet Performance Tips & Tricks Series: INDEX MATCH MATCH for Excel Lookups

Jeff Kayzerman - 23 August, 2018

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. 

See a step-by-step guide of how this works in our video here.

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:

View the video tutorial

Do you have a tricky data management problem or a spreadsheet performance issue? Send your questions – we’d love to help.

Subscribe to the Enterprise Spreadsheet Performance Blog

Request a demo to get started today!

See how our Excel-enabled cloud database can help you better manage data to drive business decisions.