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

Watch on YouTube

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