Today, in Episode #1328, Mike Girvin and Bill Jelen look at a question posed by a YouTube fan. Arranging 2 Criteria with Multiple Ranges to give us our result, Mike and Bill use the INDEX, INDIRECT and MATCH Functions to lookup the data.
Transcript of the video:
Hey, welcome back, it's another dueling Excel podcast.
I'm Bill Jelen from MrExcel will be joined by Mike Girvin from Excel Is Fun.
Mike sent just a doozy this week.
We have to do a look up to one of these one of these worksheets here Tons7.5, Tons10 and so on, based on that cell and once we get there it's a two-way look up because we have to both find the BridgeSpan and the RailwaysSpan.
So, I thought oh my gosh, this is gonna be really, really difficult, but then I noticed that all four of these worksheets are exactly identical they, they have the same values in column A and the same values in column B and it's a really nice small size table and I thought wait rather than try and complicate this well first of all I would combine all of these into one worksheet if this for me but for whatever reason we have it and so we have to deal with this I kind of came up I know you might say I'm cheating I I just built some formulas that say hey based on the bridge span they chose in the runways Bay and they chose figure out where which row has that answer so this little formula up here just does a little calculation trying to figure out which section it starts in because there's four runways bands for you spritz van and once I know that which row it ends up in I'm really kind of home free I just came back here did an indirect of whatever's in cell a2 joined with the word tons and then the ! f12 f17 that's where the answers are every time and then instead of hard-coding a five in there we will paste in my little formula to come up with the correct row and there you have it yeah not a good solution if they're going to be changing that table but I'm guessing that these are engineering numbers that have been in place for decades and here we are we just need you know a simple solution to go in and grab the value from the right sheet there you have it Mike let's see what you have thanks mr. Excel wow that is amazing right there now i have to admit something raymond thirty thousand thirty one from youtube and mr. excel message boards same a username asked this question and i just made this data up I don't know whether the data was the same on each sheet but look at what mr.
Excel date he took his data recognized a pattern and then being the math genius that he has created this amazing formula to look up the row totally amazing all right and then of course he did index and the cool thing here since there's multiple columns in essence we have multiple look up areas he did recognize that tons is on each sheet and the only thing that was changing was the actual on the sheet name was this number so he created a text string with indirect that converts this referenced as a text string to an actual reference in essence looking up different tables totally amazing now I'm going to take a slightly different tact I'm going to look at this as two criterias we have to look up and we have multiple ranges so on the 10 will have to look on the 10 sheet when we select 7.5 will have to look on the 7.5 sheet now I'm going to do this based on names so I actually want to name on sheet tons too i want to name bridge span two column bridge span two on the tons three i need to name this column bridge span three now i actually typed in these threes because the the sheet name the column names are the same on each sheet but I put a 5 at the end of each one to indicate that that's a different name for this particular column now let's highlight all of the sheets I'm going to hold Shift + f10 and i actually just want to pre highlight each sheet because i'm going to create a lot of defined names but i'm going to do it quickly with keyboard shortcuts now all this did is i selected them select that range i'm going to click over here to deselect it and you can see that each sheet is selected now i'm going to name each column the name that sits in roll row one with a keyboard shortcut ctrl shift f3 create names from selection notice it knows that the names are in the top row I'm going to click OK just to check i'm going to click the name drop-down bone just like that now i'm going to use keyboard shortcuts to jump to each sheet and then the keyboard shortcut ctrl shift f3 to get to the next sheet control page down that you can see right here with next sheet is selected and then ctrl shift f3 enter control page down ctrl shift f3 enter control page down ctrl shift f3 enter control page down ctrl shift at three enter and then you can see real quickly with those keyboard shortcuts Wow may not be hard to do without knowing those keyboard shortcuts but the point here is now I have a unique name you can see that bridge span three whereas on this other sheet that column is called bridge span 7.5 all right now I'm going to create those named columns I need to look up as a text string so I'm going to come here equals and I'm going to create a formula put that bridge span in double quotes ampersand and boom now i'm actually going to use this again copy we just been here there's our reference so i'm going to do that again here and then I'm a bad typer so I'm going to ctrl V waistbands min come here equals double quotes come to the end and control V now you can see if we change this we have our references ah but those are text strings this is a text string if we try to use that in a formula won't work no problem we'll use the indirect function that can take a text string and convert it to a reference I'm going to use the lookup function index the array well it's this one right here so i just simply put that name into indirect and if we highlight this and hit the f9 key you can clearly see it worked it's looking over at the 7.5 sheet and getting that column control-z that's the array now this is an essence how we look up different tables right come on now the next trick is our two criteria look up well I'm going to use the match function the match function is great it can tell you the ordinal position which for us is row number the lookup value well we have to look of values and I don't want to i want one so i'm simply going to join them shift seven is ampersand the join symbol now you can highlight this and f9 and see sure enough from two criteria it created one control-z now we need to do the same thing for our two columns there's our column reference column reference as text so we use indirect and we simply join it just as we did our individual criteria now when you join these two columns you can go ahead and hit f9 and see sure enough it it's going to from two columns create a single column f9 there's that semicolon in a race and tax it means go to the next row so we have created a single column from two now when you join two columns it is an array and requires the keyboard shortcut control shift enter array comma 0 match we're going to put a 0 for match type close parentheses on that close parentheses on the index control shift enter now you can see the curly brackets up there because we did control shift enter that's Excel telling us that it understands it's an array let's test this sure enough that is the correct value one other thing though whenever you have an array like this control shift enter if you don't like that control shift enter you can just slap that array we put into the index function now what does that do that argument right there can handle arrays without control shift enter if you come to the end and for the row argument you leave it blank so all I did was put a comma leave it blank it's program then to deliver not a single item from that but the entire range which in our case is an entire column then I don't have to use ctrl shift enter enter and there it is no curly brackets I'm going to test it one time to I'm going to go find the 26 and 24 and it better be 5 24 26 24 and sure enough it works all right throw it back to mr. except well that was cool there you have it uh many ways to solve every problem yours would work with any table they even if they make changes mine very specific to just quickly solve problem help everyone enjoy that we'll see you next week for another dueling Excel podcast from mr. Excel Excel is fun
I'm Bill Jelen from MrExcel will be joined by Mike Girvin from Excel Is Fun.
Mike sent just a doozy this week.
We have to do a look up to one of these one of these worksheets here Tons7.5, Tons10 and so on, based on that cell and once we get there it's a two-way look up because we have to both find the BridgeSpan and the RailwaysSpan.
So, I thought oh my gosh, this is gonna be really, really difficult, but then I noticed that all four of these worksheets are exactly identical they, they have the same values in column A and the same values in column B and it's a really nice small size table and I thought wait rather than try and complicate this well first of all I would combine all of these into one worksheet if this for me but for whatever reason we have it and so we have to deal with this I kind of came up I know you might say I'm cheating I I just built some formulas that say hey based on the bridge span they chose in the runways Bay and they chose figure out where which row has that answer so this little formula up here just does a little calculation trying to figure out which section it starts in because there's four runways bands for you spritz van and once I know that which row it ends up in I'm really kind of home free I just came back here did an indirect of whatever's in cell a2 joined with the word tons and then the ! f12 f17 that's where the answers are every time and then instead of hard-coding a five in there we will paste in my little formula to come up with the correct row and there you have it yeah not a good solution if they're going to be changing that table but I'm guessing that these are engineering numbers that have been in place for decades and here we are we just need you know a simple solution to go in and grab the value from the right sheet there you have it Mike let's see what you have thanks mr. Excel wow that is amazing right there now i have to admit something raymond thirty thousand thirty one from youtube and mr. excel message boards same a username asked this question and i just made this data up I don't know whether the data was the same on each sheet but look at what mr.
Excel date he took his data recognized a pattern and then being the math genius that he has created this amazing formula to look up the row totally amazing all right and then of course he did index and the cool thing here since there's multiple columns in essence we have multiple look up areas he did recognize that tons is on each sheet and the only thing that was changing was the actual on the sheet name was this number so he created a text string with indirect that converts this referenced as a text string to an actual reference in essence looking up different tables totally amazing now I'm going to take a slightly different tact I'm going to look at this as two criterias we have to look up and we have multiple ranges so on the 10 will have to look on the 10 sheet when we select 7.5 will have to look on the 7.5 sheet now I'm going to do this based on names so I actually want to name on sheet tons too i want to name bridge span two column bridge span two on the tons three i need to name this column bridge span three now i actually typed in these threes because the the sheet name the column names are the same on each sheet but I put a 5 at the end of each one to indicate that that's a different name for this particular column now let's highlight all of the sheets I'm going to hold Shift + f10 and i actually just want to pre highlight each sheet because i'm going to create a lot of defined names but i'm going to do it quickly with keyboard shortcuts now all this did is i selected them select that range i'm going to click over here to deselect it and you can see that each sheet is selected now i'm going to name each column the name that sits in roll row one with a keyboard shortcut ctrl shift f3 create names from selection notice it knows that the names are in the top row I'm going to click OK just to check i'm going to click the name drop-down bone just like that now i'm going to use keyboard shortcuts to jump to each sheet and then the keyboard shortcut ctrl shift f3 to get to the next sheet control page down that you can see right here with next sheet is selected and then ctrl shift f3 enter control page down ctrl shift f3 enter control page down ctrl shift f3 enter control page down ctrl shift at three enter and then you can see real quickly with those keyboard shortcuts Wow may not be hard to do without knowing those keyboard shortcuts but the point here is now I have a unique name you can see that bridge span three whereas on this other sheet that column is called bridge span 7.5 all right now I'm going to create those named columns I need to look up as a text string so I'm going to come here equals and I'm going to create a formula put that bridge span in double quotes ampersand and boom now i'm actually going to use this again copy we just been here there's our reference so i'm going to do that again here and then I'm a bad typer so I'm going to ctrl V waistbands min come here equals double quotes come to the end and control V now you can see if we change this we have our references ah but those are text strings this is a text string if we try to use that in a formula won't work no problem we'll use the indirect function that can take a text string and convert it to a reference I'm going to use the lookup function index the array well it's this one right here so i just simply put that name into indirect and if we highlight this and hit the f9 key you can clearly see it worked it's looking over at the 7.5 sheet and getting that column control-z that's the array now this is an essence how we look up different tables right come on now the next trick is our two criteria look up well I'm going to use the match function the match function is great it can tell you the ordinal position which for us is row number the lookup value well we have to look of values and I don't want to i want one so i'm simply going to join them shift seven is ampersand the join symbol now you can highlight this and f9 and see sure enough from two criteria it created one control-z now we need to do the same thing for our two columns there's our column reference column reference as text so we use indirect and we simply join it just as we did our individual criteria now when you join these two columns you can go ahead and hit f9 and see sure enough it it's going to from two columns create a single column f9 there's that semicolon in a race and tax it means go to the next row so we have created a single column from two now when you join two columns it is an array and requires the keyboard shortcut control shift enter array comma 0 match we're going to put a 0 for match type close parentheses on that close parentheses on the index control shift enter now you can see the curly brackets up there because we did control shift enter that's Excel telling us that it understands it's an array let's test this sure enough that is the correct value one other thing though whenever you have an array like this control shift enter if you don't like that control shift enter you can just slap that array we put into the index function now what does that do that argument right there can handle arrays without control shift enter if you come to the end and for the row argument you leave it blank so all I did was put a comma leave it blank it's program then to deliver not a single item from that but the entire range which in our case is an entire column then I don't have to use ctrl shift enter enter and there it is no curly brackets I'm going to test it one time to I'm going to go find the 26 and 24 and it better be 5 24 26 24 and sure enough it works all right throw it back to mr. except well that was cool there you have it uh many ways to solve every problem yours would work with any table they even if they make changes mine very specific to just quickly solve problem help everyone enjoy that we'll see you next week for another dueling Excel podcast from mr. Excel Excel is fun
