Excel - Replace 12 VLOOKUP with 1 MATCH - Episode 2028

Microsoft Excel Tutorial - Replace 12 Months of VLOOKUP with 1 MATCH

Welcome to the MrExcel podcast, where we teach you all the tips and tricks you need to become an Excel pro. In this episode, we will be discussing how to replace multiple VLOOKUPs with just one MATCH function. No more wasting time and effort on repetitive tasks, let's streamline our Excel skills and become more efficient.

As you may know, using VLOOKUP can be a tedious process, especially when you have to do it for multiple columns or rows. You can try to lock down the columns and rows with the F4 key, but you still have to manually change the third argument as you copy the formula across. This can be time-consuming and prone to errors.

Another method that some people use is using the COLUMN function to automatically change the column number as they copy the formula. However, this is still not the most efficient way as Excel has to constantly recalculate the column number for each cell. This is where the MATCH function comes in.

By using the MATCH function, we can find the row number of a specific value in a table. We can then use this row number in the INDEX function to retrieve the corresponding value from a specific column. This eliminates the need for multiple VLOOKUPs and significantly speeds up the process.

To make it even more efficient, we can add a helper column with the heading "Where?" and use the MATCH function to find the row number for each value. This way, we only have to do the MATCH function once, and then use the INDEX function for the rest of the rows. This can save us a lot of time and effort, especially when dealing with large datasets.

So there you have it, a much faster and more efficient way to replace multiple VLOOKUPs with just one MATCH function. And the best part is, you can still hide the helper column if you don't want it to be visible. Don't forget to check out our book for more tips and tricks like this. Thank you for watching and we'll see you in the next episode of the MrExcel podcast.

Table of Contents:
(00:00) Replace 12 columns of VLOOKUP with one MATCH
(00:15) The problem with using VLOOKUP for multiple months
(00:25) Workarounds
(01:00) The fastest way to solve the problem
(01:48) Inserting a helper column for Where?
(02:25) Using INDEX instead of VLOOKUP
(03:05) The speed difference between VLOOKUP and INDEX
(03:48) The problem with hardcoding the third argument in VLOOKUP
(04:07) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #excelformula #excelformulasandfunctions #excelvlookup #lookup

This video answers these common search terms:
how to lookup multiple columns in excel
how to lookup several columns using vlookup excel
how to use vlookup for multiple columns in excel
how to use vlookup on multiple columns in excel?
how to apply vlookup in excel to get multiple columns values
how to create a vlookup in excel columns
how to add multiple columns in excel vlookup
how to lookup columns in excel

Say that you have to do 12 columns of VLOOKUP
Carefully use a single dollar sign before the column of the lookup value
Carefully use four dollar signs for the lookup table
You are still hard-coding the third column argument.
One common solution is to add a row of helper cells with the column number.
Another less-efficient solution is to use COLUMN(B2) inside the VLOOKUP formula.
But, doing 12 VLOOKUP for each row is very inefficient
Instead, add a helper column with a heading of WHERE and do a single Match.
The MATCH takes as long as the VLOOKUP for January.
You can then use 12 INDEX functions. These are incredibly fast compared to VLOOKUP.
The INDEX will point to a single column of answers with $ before the rows.
The INDEX will point to the helper column with a $ before the column.

[daveblake]

This is my favourite tip so far! I have inherited some worksheets with over 1500 VLOOKUPS wrapped in IFERROR (needless to say these sheets are so slow as all 1500 VLOOKUPS have to run to test for the errors). Using this approach I can now use IF(ISNA(, this is going to eliminate over 80% of my VLOOKUPS and it is a quick fix rather then a total rewrite. Huge thanks

[Keronin]

Great tip, thanks Bill!
I'll have to remember this for the future. I luckily so far haven't had to deal with any data set large enough to make the calculation time matter, but I always would prefer to have things be efficient anyway.

[peterzhang]

thanks to you I'm running excel sessions in my company

[raymondjurado]

Index & Match. Outstanding Example.

[jameswdh]

Super interesting, but Index and matches can be really complicated. Vlookup is comfy for most situations. Loved the Video Thanks agian

[steve_yu]

Question: is OFFSET/MATCH (perhaps as an array) more efficient or less efficient than the INDEX/MATCH solution promised here?

[OzduSoleilDATA]

Is there an assumption that the lookup range can't be sorted so that a VLOOKUP/True can be used? Supposedly it only takes up to 16 checks to find the match in 1 million records.