Excel Lookup Wonders: Lookup Row & Sheet - Episode 2173
1,864 views · Published 13 November 2017 · 4:36 · Indexed 21 October 2026
Channel: MrExcel.com · 2017 · Education
Microsoft Excel Tutorial: How to look up both row and worksheet? Welcome to the MrExcel netcast, where we bring you the best tips and tricks for mastering Excel. In this episode, we will be discussing how to look up both the row and sheet in Excel. This is a common issue faced by many users, and we have the perfect solution for you. During a recent seminar in Cincinnati, Rhonda had a great question about looking up a product in a table that changes depending on the month. This means that the lookup table would be different for each month, making it a bit more challenging to find the right information. But fear not, we have the perfect solution for this problem. First, we will use the INDIRECT function to solve this issue. But before we do that, it's always easier to start with a regular VLOOKUP formula to see what the end result should look like. This will give us a better understanding of the formula we need to build. So, we start with a simple VLOOKUP formula, looking up A1 in the table for January and returning the seventh column. Don't forget to add "FALSE" at the end, as all VLOOKUPs end with this. Now, we want to get the formula text of this VLOOKUP, so we can see what the final formula should look like. This is where the trick comes in. We need to build a helper column that looks exactly like the reference in the VLOOKUP formula. This means we need to convert the date to a month and year format using the TEXT function. Then, we use Concactenation to build something that looks like the reference, and finally, we use the INDIRECT function to point to the results from the helper column. But wait, there's more! We can even copy the formula from the helper column and paste it into the VLOOKUP formula, without the equal sign, to end up with a single formula. This may seem like a lot of steps, but trust me, it's worth it. And if you're still confused, don't worry, we have a detailed explanation in our book "Power Excel with MrExcel", where we solve 617 Excel mysteries. So, to sum it up, we use the date column to figure out which sheet to use, build a regular VLOOKUP and use formula text to see what the reference should look like, build something that looks like that reference using the text function, use Concactenation to build something that looks like the reference, use INDIRECT to point to the results from the helper column, and finally, copy the formula from the helper column and paste it into the VLOOKUP formula for a single formula solution. I want to thank Rhonda for her great question and for attending my seminar in Cincinnati. And thank you for tuning in to this episode of the MrExcel netcast. Don't forget to click the "I" on the top right-hand corner for more information and to check out our book "Power Excel with MrExcel". See you next time for another netcast from MrExcel. 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/ #excel #microsoft #microsoftexcel #exceltutorial #excelhacks #excelformula #excelformulasandfunctions #excelvlookup #evergreen This video answers these common search terms: how to use vlookup in excel with multiple worksheets how to vlookup multiple sheets in excel how to reference another sheet in vlookup excel how to lookup to multiple sheets in excel how to vlookup in two excel sheets how to use vlookup with 2 worksheets in excel how to vlookup on other excel sheets how to vlookup on two different sheets in excel Table of Contents: (00:00) Look Up the Sheet and Row (00:13) Explanation of the problem and the solution using INDIRECT and VLOOKUP (01:00) Building a Helper column to mimic the reference in the VLOOKUP formula (02:02) Using INDIRECT to dynamically choose the correct Look Up table (03:35) Recap and mention of the book "Power Excel with MrExcel" (03:47) Clicking Like really helps the algorithm Use the Date column to figure out which sheet to use Step 1: Build a regular VLOOKUP and use FORMULATEXT to see what the reference should look like Step 2: Use Concatenation and the TEXT function to build a reference that looks like the table array reference in the formula Step 3: Build your VLOOKUP, but for the table array, use INDIRECT( results from step 2) Step 4: Copy the formula from Step 2 (without the equals sign) and paste in to the formula from step 3 Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153998/
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920