Master INDEX MATCH in 60 seconds

Home/Data Handling, Functions, Productivity/Master INDEX MATCH in 60 seconds

INDEX MATCH is great but GETMATCH would be better

I’ve written a number of articles in the past around how INDEX MATCH is a technically better option compared to VLOOKUP.

However, it is a trickier formula and therefore not as widely used, so I’ve added a new formula suggestion to the Excel User Voice forum.

The Excel User Voice forum allows us users to have our say on what the future of Excel holds.

Please take 30 seconds to vote for my suggestion of a simpler, safer LOOKUP formula

Please vote here

***Thanks to everyone who has voted for this so far, we’re now in the top 10 ideas on Excel User Voice – please share / retweet etc to encourage others to vote and push this into the top 10 to make it happen ***

In the meantime here are 3 reasons why you should use INDEX/MATCH instead of VLOOKUP        

  1. You can “lookup” to the left easily
  2. You don’t get incorrect results when a column is inserted or deleted from your data
  3. When used in conjunction with Tables, the formulae are a lot more meaningful.

How to set it up in 60 seconds

But INDEX/MATCH is more complicated than VLOOKUP I hear you say,  and I agree. So here’s how to master it in 60 seconds

=INDEX( Step3_Result_Column, MATCH( Step1_Lookup_Cell,  Step2_Lookup_Column, 0),0)

Copy the above formula and paste it into your Autocorrect window (Excel – Options – Proofing – AutotCorrect Options)

I use iii in the Replace Box (see image)

Now whenever you need an index match, type iii,  AutoCorrect kicks in and you have a ready-made formula.  All you need to do is just double click on each part of the formula and then select what you need at each stage. I’ve numbered the parts to indicate the best order to do the “double clicking”.

Once you get the hang of this you’ll never need a VLOOKUP again

Also, here’s a link to vote for the Excel team to provide us all with a simpler lookup formula.  It ‘s almost at number 10 in the most requested changes so please VOTE! to push it further up the list.

Please vote here

2017-03-08T13:10:40+00:00 Data Handling, Functions, Productivity|