Lookup Row & Sheet

Learn Excel from MrExcel Podcast, Episode 2173: Look Up the Sheet and Row.

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. I was in Cincinnati last week, and Rhonda in Cincinnati had this great question. Rhonda needs to look up this product but the Look Up table is different, depending on which month it is. See, we have different Look Up tables here for January through April, and presumably, for the other months as well. Alright.

So I'm going to use INDIRECT to solve this, but before I do INDIRECT, I always find it easier just to do a straight VLOOKUP. So, we can see what the form is going to look like. So we're looking up A1 in this table on January, and we want the seventh column, comma FALSE, all VLOOKUPs end in FALSE. (=VLOOKUP(A2,'Jan 2018'!A1:G13,7,FALSE)). Alright.

And, well, that's the right answer. What I'm really interested in, is getting the formula text of that. So it shows me what the formula's going to look like. And the whole trick here is, I'm trying to build a Helper column that's going to look exactly like this Reference, right? So this part-- just that part right there. Alright. So this Helper column has to look like that thing looks. And the first thing I want to do, is we're going to use the TEXT function of the Date-- the TEXT function of the date-- to get mmm, space, yyyy-- so, "mmm yyyy" like that-- which should return, for each of the cells, what month we're looking up. Now, I need to wrap that in apostrophes. If there hadn't been a space name in there, I wouldn't need the apostrophes, but I do. So we're going to Concactenate up front, the apostrophe, so that's quote-- apostrophe, quote-- ampersand, and then over here, another quote, apostrophe, and exclamation point, A1:G13, closing quote, ampersand there.

Alright. So now, what we've successfully done over here in the Helper column, is we built something that looks exactly like the table array in the VLOOKUP. Alright. So our answer, then, it's going to be =VLOOKUP of this cell, A2, comma, and then when we get to the table array, we're going to use the INDIRECT. INDIRECT is this cool function that says, "Hey, here's a cell that looks like a cell reference, and I want you to go to F2, take the thing that looks like a cell reference, and then use whatever is in that cell reference as the answer," comma, 7, comma, FALSE," like that. (=VLOOKUP(A2,'Jan 2018'!A1:G13,7,FALSE)) Alright, so now, on the fly, we're choosing a different Look Up table and returning the values depending on whether it's April or what.

Alright. So let's take this 4/24, I'll change it to 2/17/2018, like that, and we should see that 403 change to 203-- perfect. It is working. Alright. Now, we don't need these two columns here, of course, and really, if you think about it, we don't need this whole column. We could take that whole thing, except for the equal sign, Ctrl+C to copy it, and then where we have D2, just paste, like that. Perfect. Double-click to shoot that down and get rid of this. There is our answer. Alright, we'll use our formula text here, just to take a look at that final answer.

I have to tell you, if I had to build that formula just from scratch, I wouldn't do it. I wouldn't be able to do it. I would screw it up, for sure. That's why I always build it in steps-- I figure out what the formula's going to look like and then Concactenate the Helper column that will be used inside the INDIRECT, and then finally, maybe here at the end, put everything back together.

Hey, many tips like this tip in the book, Power Excel with MrExcel. This is the 2017 Edition with 617 Excel mystery solved. Click that "I" on the top right-hand corner for more information.

Alright, wrap-up from this Episode: Rhonda from Cincinnati-- how to look up both the row and the worksheet. I use the Date column to figure out which sheet to use; so I build a regular VLOOKUP and use formula text to see what the reference should look like; and then build something that looks like that reference using the text function to convert the Date to a Month and Year; use Concactenation to build something that looks like the reference; and then when you build your VLOOKUP for the second argument, the table array, use INDIRECT; and then point to the results from step 2; and then the optional fourth step there, copy the formula from step 2, without the equal sign, and paste it into the formula from Step 3, so you end up with a single formula.

Well I want to thank Rhonda for showing up to my seminar in Cincinnati, and I want to thank you for stopping by. I'll see you next time for another netcast from MrExcel.