In our recent posts here at xOverTime we have been exploring the power of 3, with 3 take-aways from a recent conference, 3 reasons IT teams should look to leverage spreadsheets in the enterprise, and 3 ways to move the needle on enterprise spreadsheet performance. Sometimes though I think it can be as simple as 1 trick.
Today we kick off a series of tips and tricks, so that with the right trick, you can get your unwieldy spreadsheet data back on track. For example, the Excel INDEX MATCH function is one of Microsoft Excel's most powerful features that allows you to easily look up a specific value. It’s the older brother of the much-used VLOOKUP, which many of you will be familiar with.
Let’s take a moment to look at how the INDEX MATCH functions work. Firstly, the INDEX function returns the value at the intersection of a column and a row. The syntax for the INDEX function is:
In plain English this means for you and me:
=INDEX(the range of your table, the row number of the table that your data is in, the column number of the table that your data is in)
INDEX will return the value that is in the cell at the intersection of the row and column you specify.
On its own, INDEX is only half the answer. With only the INDEX function you have to hard key the row and column number, and that’s why it works better with the MATCH function. The MATCH function finds the position of a value in a list. A list can either be in a row or a column.
The syntax for the MATCH function is:
=MATCH(lookup_value, lookup_array, [match_type])
Again, in plain English for you and me:
=MATCH(find what row ‘something’ is on, in a column range, match it exactly)
Before putting it all together, the INDEX function returns the value in the cell you specify, and the MATCH function tells you the column or row number for the value you are looking up.
INDEX MATCH together:
The INDEX and MATCH functions are a popular alternative to the VLOOKUP. INDEX MATCH allows you to look up values in a table based on other rows and columns, adding flexibility and performance beyond what’s available in VLOOKUP.Take a moment to see INDEX MATCH in action with a working example:
Do you have a tricky data management problem or a spreadsheet performance issue? Send your questions – we’d love to help.