Great news! XLOOKUP is going to replace INDEX MATCH eventually but in the meantime…..

We’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.

In May 2016 Wyn, added a new formula suggestion to the Excel User Voice Forum , where users can influence what the future of Excel holds.

At the end of August 2019 XLOOKUP was announced,  a much cooler name than GETMATCH and with even more functionality.   

It may take a while for everyone to get XLOOKUP as Microsoft gradually starts rolling it out to the wider O365 community. In the meantime this article will make it much easier for you to create INDEX MATCH formulas.

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( Step3ResultColumn, MATCH( Step1LookupCell,  Step2LookupColumn, 0))

Copy the above formula and paste it into your Autocorrect window (Excel – Options – Proofing – AutotCorrect Options) I use iii in the Replace Box.

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!

If you want to read about XLOOKUP click here…