Excel Magic Trick 824: Two Way Lookup With More Than One Lookup Table Same or Different Sheets

welcome to Excel match trick number 824 if you want to download this workbook excel metric 823 - 825 click on the link below the video in this video we want to do a two way lookup so right now we have gross pay of 232 and one allowance so we need to go down here find the row and the column and then return that two to the cell but here's the twist we want to be able to this is single weekly if I change this to married weekly I want the formula to jump over to the next table right so so matter no matter what I put in this cell this cell this or this cell I want it to look up amongst four tables so it's a two way look up looking up from multiple tables now in our last video we saw how to do this conditional formatting this video we'll just see how to do the lookup formula let's come over to this sheet 824 now we're gonna do a bunch of cool things in this video including see a few looking at a few different formulas now this situation we have all the tables on one sheet sometimes you don't have it you have all the tables on different sheets okay so we'll see different formulas for that now it's stored off I just I didn't use define name feature but I'm going to call this table single weekly this table marry weekly this table single monthly and this one married monthly for taxes right so this is like a wage bracket lookup to find the tax for a particular income right so what I did was I down here I type single weekly married weekly single monthly married monthly and I'm going to designate this table is 1 this table is - this table is 3 on this table as for now in order to do that and I should have removed this before I did that since we're going to use these words and they're important that they're spelled correctly and don't have extra spaces I'm going to do data validation right so I have marital status down here in pay period so here I'll use the keyboard shortcut alt DL tab and instead of allowing any value I'm going to say a list tab and I'm going to select this these two right here now here I only have married and single right but you could have lots of other categories or the ones required by in your tax situation all right click OK and then here we go LTL oath the L tab L tab and then the pay period is weekly and monthly again you could have however many you want so we'll select weekly and single now the trick is going to be I have some conditional formatting that's incomplete because we don't have all the information here but later it'll it'll work just as we saw in last video now single weekly that means I want this table and I've designated this one if I change this to married then I need our formula to know this as table to write for looking up because we're not only doing a two-way lookup we're looking to doing a two-way lookup to multiple tables so what it does are the table names right and so I'm going to in a separate cell put the number one two three or four and actually we'll see that you can use the index function and it understands tables as one two three four or the choose function it will also understand different things as one two three four so I need a formula for that so I'm just going to do straight vlookup I'm going to look up these two items compare them to what's in this column and then return one of these numbers to the cell so equals vlookup lookup value well these are in two separate cells and for example married weekly that's a single item text string right no problem I go that and shift 7 which is ampersand to join simple that that automatically joins those of you who were to f9 you can see look that works control-z lookup value comma and the table this is our table first column second column the second column has the number I want to return so I comma and type a2 comma and this is false because we're looking up words that are not sorted so instead of false I'm going to put that means exact match all right so that's working now this this is a formula element we have it in a separate cell it could certainly be inserted into our larger formulas later if you wanted that now we're going to use a couple different methods to choose which table but the choose function is a great way because if you choose your table with a choose function the tables can be either on this the same sheet as the formula or different sheets all right I want to see the basics of the choose and I'm going to do a silly little thing which will actually use to our advantage later notice if I say single weekly the minimum income from our table is 200 so I want to show 200 here if I was over if I selected this the minimum value for married weekly in our tables would be 300 then I was 200 300 the minimum here would be 500 and 750 so I want to show you how to use the choose function to just choose amongst 4 different values all right so you're ready equals choose choose is great you give it an index number 1 2 3 4 5 6 7 in our case 1 2 3 4 and then you put in the values separated by commas that's different than like vlookup or match where you highlight a range right and the great thing about choose is these values can be text numbers formula ranges all sorts of things alright there's our index number because we ultimately need the min from this now watch this comma well if I really only want the min then what are the 4 things that need to be in this choose either that comma that comma that comma that and you could also do this across sheets right sheet references can be in here as the values right so all choose does is it looks here it says 2 so it's going to go oh the second one which of course is 300 if I were to change this to single there it is choose is doing its work it's looking up from the first item it was given a 200 so it shows 200 similarly we could do choose this comma and for the max value comma this comma this comma this now this is a sort of silly little example but it does illustrate the power of choose right you can actually put number cell references ranges formulas all sorts of things to choose from one other note that if you actually have the tax tables right they'd start at zero and go up to some huge number and they might all be the same all right now just for kicks for this to illustrate this and have everything work fluently I'm going to use the randbetween function um say for the gross pay please select from between the middle that's the bottom and the top that's the maximum right what's cool about this and you'll see the conditional formatting now works right allowances are 0 but if I were to change this to Mary now that cell right there is choosing between the two maxes there so I'll never get the wrong value in essence up here now again for payroll you never do that you're typing the number in alright but I'm just being silly here but we did get to illustrate the choose it can choose amongst different cell references alright so I'm going to do equals ran between here for my allowances I only have 0 to 5 again a tax table head 0 to I don't know 10 or something like that so I can just use ran between and type in some numbers all right so now if I hit the f9 and guaranteed to get only things from this table right here I change this alright yeah I know that's silly all right vlookup all right lookup value it's going to be that comma and where's our table remember what do we have one of four tables so we're going to use choose again choose where as the index right there now remember inside of choose it's going to get one two three four , and what are the values we either want this table that's number one comma this table that's number two comma this table that's number three or this table I notice this time we're putting ranges in so we get in this video to illustrate you know two great uses for choose these are either single cells with values or whole ranges so what choose is doing choose it's going to dump a lookup table into the vlookup function now comma what's the column index now if we had if we were looking up words that weren't sorted or something like that we have to use index and math or no we'd have to use match here we could actually use match inside if you look up but I want you to notice something this is allowances and when I say one what do I really want well vlookup member vlookup is that purple thing so this is one two three four well what do we have to add to one to get four because that's the fourth column three what if this was two what's two plus three one two three four five so all we have to do is say allowances for the look the column index plus three and that's it that's our formula we're doing approximate lookup in this first column here so we do not have to put a a one there or a true because by default vlookup will do approximate look up all right you got to be kidding me it's not working let's try it let's do Mary and then I'm going to hit f9 to test it all right so f9 there's a three every time looks like it's working wow that is magic so that's that's one problem my favorite amongst all the formulas if I'm on the same sheet now what about from a different sheet ho guess what the same exact formula will work we're just going to change the input to the chew so instead of cells on this sheet there will be cell references from a different sheet so I'm going to equals vlookup my lookup value is going to be this comma and choose choose is going to get this table number comma and the only difference is we will select ranges from our other sheet so I'm going to click on SW and highlight this range you can see up here there's the sheet reference the explanation point in the range value 1 comma value to highlight the range value 3 I click on the sheet SM I'll highlight that I through comma I click on the sheet mm I like the range and that's it value 4 is in so I close parenthesis now comma I'm just going to type a 2 here and fix it over on the other sheet and close parentheses control enter and then I'm going to fix this I didn't really want a 2 here I wanted a number of allowances plus 3 all right so now it's just looking this form is just looking to that other those other sheets if I hit f9 they're all getting the same thing if I change this to weekly sync single weekly all right now that is this is a great solution and same with this one if we have incremented numbers like this but what if we really had different ranges here and in particular here if these weren't the same exact values right what are we going to do that means our this formula here choose is choosing amongst that and that well if you had different values here well we saw how the vlookup could do approximate match but if they're different values all together and you're doing exact match or here these weren't 0 1 2 3 this was 0 1 2 through 5 but this was 7 8 9 10 which of course in this tax example would not happen but another situation where the actual look at values are different on each table that really complicates the formula but let's see how to do it and involves using the choose function a bunch of times because think about it if these are all the same but if they were different we'd have to choose different ranges each time depending on what is in this cell which is of course dependent on this so we just have to use the choose a bunch of times all right you don't need to do this formula for this example but for some of you that have totally different lookup values this will work equals index now index this one right down here we don't usually use that one but it says reference which means you got you put in some cell references are four different tables and then you have to give it the area number so index is kind of like choose and index for two-way lookup thrown together so watch this I'm going to in parentheses now this reference argument here if you put four different ranges which we're going to do separated by commas and you don't enclose it in parentheses it doesn't work all right I'm just getting just the intersecting value here right comma comma comma okay and then I have a I need to put a close parenthesis here and we're following this one that's the reference now the row number well now I'm going to have to use match to no proximate here and then an exact here so I'm going to in this argument right here do match oops now the lookup value what am I looking up for each one of the different at least ranges I'm looking up gross pay comma and oh I have not just one lookup array but many so I use the choose here inside of the match if only I could type index number it's going to be that comma and then I just highlight the values the range is 1 2 3 4 close parenthesis this is going to be a an approximate match so I can leave that argument off close parenthesis on the match now I'm - I just finished row that's this whole thing right here comma and now I get to the column and this is going to be match again I'm looking up a number of allowances comma and now I have if all of these numbers were different for each one I would have to put choose again index number is this comma and then I simply highlight 1 comma 2 comma come on that's the fourth value so I close parentheses that is uh one more because this well these are sorted but let's assume that they we had to do an exact match so I'm going to come out and put a zero there's the column number but watch this comma and now we need to tell the index from the original two-way lookup ranges we need to tell it what the area number is now that while formula is the most robust because it can and especially with the two matches you have a lot of latitude in how you look something up all right now we're going to get the same values here of course F 9 f 9 f9 and if we change this to Mary how about Mary monthly so we should be getting the right values over here all right now again I like that one or this one this one's pretty good too when you have those different circumstances but you could use this is probably even the shortest and the simplest to understand but it involves using the indirect function and sometimes on in big spreadsheets since the indirect function is volatile it calculates every time you do something so we can slow down calculation time but no problem let's look and think about this married monthly again we're choosing from a drop down and we need to choose the right table all right so I'm going to actually do this formula right here using indirect and I'm going to go grab the right table from a different sheet now let me see if I can make this a little bit bigger all right now over on this sheet what we're going to do is there's some text here and I'm going to be smart and I'm going to go name those tables each one of the tables on a different sheet like single monthly married monthly alright and then we use the indirect to grab those define names so I'm going to come over here I'm going to highlight very carefully not the labels at the top not this but just that's range right there I'm going to come up to the name box and this one's actually watch this I'm going to cheat since I'm such a bad type er I'm going to copy that because I've already already verified that those are correct I highlight the table I click in the name box and control V enter and then I'm going to come over to the next one highlight that copy escape highlight that click in the name box control V enter if only I knew how to type single monthly controls see escape highlight hopefully I'm highlighting the right range control V enter alright and I want this one to be named married monthly click on the name box ctrl a ctrl V enter now I'm going to test this married weekly all right looks like I got the right one single monthly looks like I got the right one single weekly what single monthly okay so it looks like they're all correct now I'm going to come back over here those are defined names right that's considered a reference that's as if that that's what we have in place of the range a 4 2 H 13 so I'm going to come back over here alright now we're going to use vlookup of course but we're going to use the indirect function to choose the table now notice what happens we change this well that's text in the cell but the indirect function exists its sole purpose is to take text and convert it to a reference so in this cell I'm going to do equals vlookup the lookup value is our gross pay the table array I'm going to use indirect what's the reference as text so they name that argument pretty smartly it's this ampersand join symbol and that close parenthesis and that's it now let's just look at this highlight this and hit f9 it is text you can see it's a text string because it's got double quotes ctrl Z but when I highlight this whole range and the indirect f9 it shows me all the numbers from that particular table ctrl Z all right so there's the table array comma and the column index it's going to be the same thing allowances plus 3 and I don't need the last argument close parenthesis so that's amazingly short look at that it is volatile every time you do an action that indirect recalculates right now I'm going to hit f9 that looks like they're all working the same change it to married oh that's amazing now one last one notice on each one of these sheets is from a 4 to a CH 13 a 4 to a CH 13 I better write to a 4 : h 14 all right now it's if we're doing a sheet reference it would look like this and I can't get the same one because that's now got a define name but I'm going to do one to two few so I don't see that defined name right so the the way the syntax works for a sheet reference is SW explanation point now that SW is the sheet name if you had spaces you'd have to put single quotes here right but explanation point and then that range all right that value R is just saying you can't put a range of cells into a single cell now how did I get this as I change from married to single monthly it's changes to SM I just used two left's so left of one character of that ampersand left character of that so now anytime I choose that it will give me the name of the sheet now notice I had to name these sheets smartly too so equals vlookup lookup value is going to be this comma the table I'm going to use indirect the reference as text we're going to build it that and then ampersand and then in double quotes explanation point a4 : h 13 I hope I got that right in double quote close parenthesis I'm going to highlight this and see what I get f9 oh it looks looks good looks good control Z to undo that alright so there's the table comma and then column index allowances plus three close parenthesis now if I hit f9 looks like they're all the same I change it to married monthly I change it to marry weekly they're all the same that's just absolutely amazing okay so that was a lot about how to do lookup from different tables this one's fine the choose and vlookup straightforward on the same sheet or a different sheet again if you have things from different sheets the index function won't work it doesn't like that in fact in help it says it these ranges for index I'll have to be on the same sheet this is the most robust if it's all on the same sheet because you can do your you have multiple you don't have you're not stuck with the same columns each time here you can have different lookup data there and then finally we saw some variations on in direct alright we'll see you next video

Channel: ExcelIsFun

Views: 31,231

Rating: 5 out of 5

Keywords: Excel, 2007, 2010, excelisfun, Mike, Gel, Girvin, Highline, Community, College, Slaying, Dragons, Two, Way, Lookup, With, More, Than, One, Table, Same, Sheet, or, Different, Sheets, VLOOKUP, and, CHOOSE, function, method, INDEX, MATCH, INDIRECT, Defined, Names

Id: WogFiO3aOas

Length: 24min 27sec (1467 seconds)

Published: Sun Nov 13 2011