Shawn has to do a four-way lookup: Worksheet, Row, Set of Columns, Column within the Set. To get the correct LOOKUP result, Bill shows a method using INDEX and INDIRECT to solve the problem posed in Episode #1413. Learn Excel 2010 by Bill Jelen
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1413, Four-Way LookUp.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in by Sean.
This is insane, Sean has to do a four way look up based on whether this says three percent or seven percent.
He has to go to this, this sheet or this sheet.
All right! so, that's the first bit of look up which sheet are we going to I'm gonna solve that using indirect we have to figure out how many licenses that's down the left hand side and what is really very nice is Sean made both of these sheets be exactly identical.
So, once I figure out the row number and one it's the row number and the other one and then so, then the next to look ups are silver and annual check this out the way that it works is annual starts in column 5 and quarterly starts in column 9 and monthly starts in column thirteen and then within that platinum, gold, silver and bronze kind of identify the position.
So, I'm gonna figure out what row number I'm in, what column number I'm in, I'm going to use the INDEX, but to further complicate things we have to make sure that the INDEX is using indirect to point to all of the cells.
Now, I'm here on the three percent sheet, I'm going to do control page down that goes to the seven percent sheet, control page up,page down, page up, page up make sure that all of those positions are the same that's really important for the way that I'm going to solve this I came out here and kind of built some temporary cells this is you know one of these really complicated formulas I rather build it in pieces.
So, the sheet name that actually turns out I check that is 0.03 being returned by that formula.
So, I use the TEXT function.
The TEXT of D4 and then in quotes '0%' end quote end parenthesis that gets me the sheet name with the parentheses around it, which I'm going to need for INDIRECT.
The row number, okay I actually kind of cheated here the value for 300 licenses is on row 303 it just so happens that there's one to three thousand rows that start in row four.
So, I just simply added three to that number that was the simplest part and then the column number check this out i actually did 2 VLOOKUP's up here the first VLOOKUP is to find annual quarterly or monthly and I said that there are column is going to be either 4, 8 or 12.
The annual let's take a look annual actually starts in column 5.
So, I said we're going to go one before that if its annual, we are gonna start looking in column 4.
If its quarterly, we're going start looking in column 8 and monthly out there in column 12 and then a second VLOOKUP, a second VLOOKUP here based on the plan there in found the position within the annual the first, second, third or fourth column.
So, as these values change silver annual and so on that column number will change.
All right! now, once I know those three pieces I could build a formula like this equal INDEX of and let's just go to the three percent sheet, it's like this big huge range you're going from row one all the way down to row three thousand and three comma and you know, what row I want, I want row 303, what column I want, I want column 7 that will return the answer.
Now, of course instead of hard-coding the 303 and 7, I'm gonna point to those 2 cells.
So, let's do that choose the 303, click on that cell, I'll choose the seven click on that cell.
All right! so, that's all good but we still have this final little thing that's just driving us crazy here in that we have to go to the either the three percent sheet or the seven percent sheet.
So, as you look at this formula here three percent that's an apostrophes the ! and then A1 to P actually could have been 3,004, this one too far there I want to replicate that reference using either three percent or seven percent and the way I'm gonna do that is a very cool function called INDIRECT.
So, the INDIRECT says hey, in these parentheses I'm going to generate some text that text is going to look exactly like a cell reference.
So, in this case I three percentage three percent percentage the ! A1 to Z3000 and that all work.
So, then we can choose client number 2, client number 3, these values update and all of the lookups go find the correct value here for answers.
So, kind of a kind of an amazing little 4-way look up using INDEX first, knowing with INDEX we could just find the row number in the column number that we're looking for and then the final thing is INDIRECT to figure out which sheet name to use.
Sean, certainly not a fun, fun problem.
I wanna thank Sean for sending that in.
Wanna thank you for stopping by.
We'll see you next time another netcast from MrExcel.
Learn Excel from MrExcel podcast episode 1413, Four-Way LookUp.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in by Sean.
This is insane, Sean has to do a four way look up based on whether this says three percent or seven percent.
He has to go to this, this sheet or this sheet.
All right! so, that's the first bit of look up which sheet are we going to I'm gonna solve that using indirect we have to figure out how many licenses that's down the left hand side and what is really very nice is Sean made both of these sheets be exactly identical.
So, once I figure out the row number and one it's the row number and the other one and then so, then the next to look ups are silver and annual check this out the way that it works is annual starts in column 5 and quarterly starts in column 9 and monthly starts in column thirteen and then within that platinum, gold, silver and bronze kind of identify the position.
So, I'm gonna figure out what row number I'm in, what column number I'm in, I'm going to use the INDEX, but to further complicate things we have to make sure that the INDEX is using indirect to point to all of the cells.
Now, I'm here on the three percent sheet, I'm going to do control page down that goes to the seven percent sheet, control page up,page down, page up, page up make sure that all of those positions are the same that's really important for the way that I'm going to solve this I came out here and kind of built some temporary cells this is you know one of these really complicated formulas I rather build it in pieces.
So, the sheet name that actually turns out I check that is 0.03 being returned by that formula.
So, I use the TEXT function.
The TEXT of D4 and then in quotes '0%' end quote end parenthesis that gets me the sheet name with the parentheses around it, which I'm going to need for INDIRECT.
The row number, okay I actually kind of cheated here the value for 300 licenses is on row 303 it just so happens that there's one to three thousand rows that start in row four.
So, I just simply added three to that number that was the simplest part and then the column number check this out i actually did 2 VLOOKUP's up here the first VLOOKUP is to find annual quarterly or monthly and I said that there are column is going to be either 4, 8 or 12.
The annual let's take a look annual actually starts in column 5.
So, I said we're going to go one before that if its annual, we are gonna start looking in column 4.
If its quarterly, we're going start looking in column 8 and monthly out there in column 12 and then a second VLOOKUP, a second VLOOKUP here based on the plan there in found the position within the annual the first, second, third or fourth column.
So, as these values change silver annual and so on that column number will change.
All right! now, once I know those three pieces I could build a formula like this equal INDEX of and let's just go to the three percent sheet, it's like this big huge range you're going from row one all the way down to row three thousand and three comma and you know, what row I want, I want row 303, what column I want, I want column 7 that will return the answer.
Now, of course instead of hard-coding the 303 and 7, I'm gonna point to those 2 cells.
So, let's do that choose the 303, click on that cell, I'll choose the seven click on that cell.
All right! so, that's all good but we still have this final little thing that's just driving us crazy here in that we have to go to the either the three percent sheet or the seven percent sheet.
So, as you look at this formula here three percent that's an apostrophes the ! and then A1 to P actually could have been 3,004, this one too far there I want to replicate that reference using either three percent or seven percent and the way I'm gonna do that is a very cool function called INDIRECT.
So, the INDIRECT says hey, in these parentheses I'm going to generate some text that text is going to look exactly like a cell reference.
So, in this case I three percentage three percent percentage the ! A1 to Z3000 and that all work.
So, then we can choose client number 2, client number 3, these values update and all of the lookups go find the correct value here for answers.
So, kind of a kind of an amazing little 4-way look up using INDEX first, knowing with INDEX we could just find the row number in the column number that we're looking for and then the final thing is INDIRECT to figure out which sheet name to use.
Sean, certainly not a fun, fun problem.
I wanna thank Sean for sending that in.
Wanna thank you for stopping by.
We'll see you next time another netcast from MrExcel.
