Excel - How To Do A Three Way Lookup Excel - Duel 159 - Episode 1895

2,810 views · Published 11 July 2014 · 5:39 · Indexed 1 November 2026

Channel: MrExcel.com · 2014 · Education

Watch on YouTube

Microsoft Excel Tutorial - Three-Way Lookup in Excel.

Welcome back to another episode of Dueling Excel Podcast! I'm Bill Jelen from MrExcel and I'm joined by Mike Girvin from Excel is Fun. In this episode, we tackle a three-way lookup question sent in by a viewer on YouTube. They wanted to know if there was a way to do a three-way lookup for all the months in a property dataset without any duplicate records. 

While the viewer specifically asked for a lookup solution, I believe this can easily be solved using the new =SUMIFS formula due to the absence of duplicate records. We start by defining the SUM range, which in this case is the range of values we want to add up. Then, we specify the criteria range 1, which is the range of properties we want to search through. We use the F4 key to lock the rows but not the columns, allowing us to drag the formula across and automatically adjust the columns. 

Next, we add the criteria range 2, which is the range of sections we want to search through. Again, we use the F4 key to lock the rows but not the columns. Finally, we add the criteria range 3, which is the range of months we want to return. As we drag the formula across, the month will automatically change based on the columns we are referencing. This simple and efficient solution gives us the desired result without the need for a third lookup. 

But, as always, Mike has a different approach to the problem. He goes straight for the D functions and uses the =DGET() function to perform the lookup. This function allows us to specify the entire dataset, the field we want to retrieve data from, and the criteria we want to search for. Mike also shows us how to use relative cell references and the IFERROR function to make the formula more dynamic. 

However, when we compare the calculation times using the FastExcel V3 tool, we see that the =SUMIFS formula is almost twice as fast as the =DGET formula. This may not make a significant difference in this particular case, but it could be a game-changer if we were dealing with a larger dataset. In the end, both solutions work well and it's up to personal preference which one to use. Thank you for tuning in to this episode of Dueling Excel Podcast, we'll see you next week for more Excel tips and tricks from MrExcel and Excel is Fun.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Is there a way to do a 3-Way Lookup for all the months?
(00:22) Using =SUMIFS formula to solve the problem
(01:34) Explanation of the formula and its components
(02:05) Alternative solution using =DGET function
(03:06) Adjusting the formula to handle errors
(03:39) Comparison of the two solutions using FastExcel
(05:22) 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 #formulaspeed #excelsumifs

This video answers these common search terms:
how to do a three way lookup excel
what is a two-way lookup in excel?
how to use dget function in excel
how to use sumifs in excel
multiple lookup criteria in excel

The dueling Excel podcasts return with a new theme song from Sziliva Juhasz, a new voiceover from Charles "Fast" Williams, a D function from ExcelisFun, and more in this three-way lookup problem.

Join the MrExcel Message Board discussion about this video at  https://www.mrexcel.com/board/threads/1153719/

More from this channel