Master INDEX MATCH in 60 Seconds
Wyn Hopkins
Leading a team that solves data challenges for companies and people Power BI | Excel | Fabric | Solver Budgeting & Consolidation. Control costs, reduce risk and assist growth Microsoft MVP ??5 Million+ YouTube views
Thanks to everyone who voted here XLOOKUP will be available to all O365 subscribers by the end of July 2020 (fingers crossed). If you are on the Monthly release channel you have it as of 1st Feb 2020.
?For more on XLOOKUP click here
In the meantime, if you need to use INDEX MATCH then read on...
Here are 3 reasons why you should use INDEX/MATCH instead of VLOOKUP
- You can “lookup" to the left
- You don’t get incorrect results when a column is inserted or deleted from your data
- 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.
If you'd prefer to watch a video then click here or carry on reading instead
So here's how to master it in 60 seconds
Copy this "formula":
=INDEX( Step3ResultColumn, MATCH( Step1LookupCell, Step2LookupColumn, 0),0)
and paste it into your Autocorrect window (Excel - Options - Proofing - AutotCorrect Options)
I use iii in the Replace Box and paste the formula in the "with" 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
Wyn
AMAZING EXCEL SOLUTIONS
Business Analyst | Data Analyst | Finance Analyst | Business Intelligence
6 年This is great, falling in love with INDEXMATCH. Chinasa Mbachu, ACA you need to see this
Financial Reporting Analyst at SOCAR Romania I Passed CFA Level 1
7 年{=INDEX(RESULT_RANGE,MATCH(1,(CRITERIA_RANGE1=CRITERIA1)*(CRITERIA_RANGE2=CRITERIA2)...,0))} This formula is the best solution for multiple criteria search in excel. But it makes excel to run too slow.
Data Management Expert | Data Analyst | Excel Trainer | PowerApps Developer | Author | Project Manager | Excel Microsoft MVP
8 年As pointed by Kenneth Barber Index n match can replace HLookup..see article https://crispexcel.com/hlookup-index-match-or-vba/
Mining Executive, Mining Engineer, Financial Modelling
8 年Wyn VLookup has always been able to lookup to the left ie: =VLOOKUP("b",CHOOSE({1,2},C3:C6,A3:A6),2) or =VLOOKUP("b",CHOOSE({2,1},A3:A6,C3:C6),2) Range A3:A6 is to the left of C3:C6 put the values a..d in C3:C6 put any values in A3:A6 I wrote about this in 2012 https://chandoo.org/wp/2012/09/06/formula-forensics-no-028/