Excel Magic Trick 811:Multiple Two Way Lookup Adding INDIRECT, ADDRESS & Array MATCH Function

welcome to Excel magic trick number 811 hey if you want to download this workbook excel magic trick 806 to 811 click on the link below the video o this video this is one of the coolest tricks I've seen in a long time circle chicken at mr. Excel message board there's the link if you want to download it right there posted this solution it was just so cool now here's the setup we have a two way lookup and the person said that they had a data dump like this a trial balance from an accounting system so it always came like this and they absolutely they were given a list they were given a list of account numbers and departments and they had to have a single cell solution to add so in essence they wanted to do a two-way lookup right so you have to look up this and figure out the row number so that B Row one and then this that B column two and then take that number right that's a two-way lookup but they needed to do two way lookup for lots of items and then Adam alright let's look at the long hand method of doing this we can do a two-way lookup using index and match so the array index is great you can have a two-way array because in order to find an intersecting cell you need a row number and a column number I'm going to hit f4 comma and then the row number and column number this setup is perfect for match function because match can look up a number and tell us which row or column so I'm in a four row number use the match function lookup value is going to be this comma the lookup arrays within this now right now it's going to look up that and say one right and that's what we want that's what the index needs right here is a1 for it's two-way lookup and I'm gonna go call a zero we are these numbers all look like they're sorted so we could get a right away without out it but let's leave that there comma and then match okay so now we need a column never I'm going to look up this comma within this array right here and I'm going to hit the f4 key comma zero the reason why I put them zeros because there's a little alternative at the end where we're gonna need exact match and it but if they're sorted and you're only using this range you do not have to put this in the default is approximate lookup and in the sense the numbers are sorted it will work all right so now let's just take a look at this this should give us a 1 if I hit f9 ctrl Z to undo that and this one should give us f9 so give us a to ctrl Z all right so that's the longhand method right and doing this longhand method if you know anything about a Rae former should maybe give you an indication of how we might be able to build an array formula and we'll talk about that in just a moment but all it did is look that up we dragged it down here so it's looked up each one of these right so this 640 300 is to the 412 640 so right there and then you add now normally when you see a column of formulas like this you can look at this and go okay well here I looked up an individual number right there and here I looked up an individual number right there so really it's like if we could throw the whole column the values into there maybe we could build an array formula and that's how I thought at first and actually circle chicken enough and a couple other people were posting and said yeah I tried that also and it didn't work alright so I would think that we could go like this right and then that's I would assume if I highlighted this in the hit f9 it would give us all these intersecting values when you have nine no way now maybe I could the evaluator doesn't work so I'm gonna try it with some maybe you know sometimes the formula tire gives you a value error but really if you put it inside another function won't work I'm going to ctrl shift enter since this is an array and I only get the first number right and I actually posted an ask man does anybody know I mean I'm assuming that what's happening here is the array is programmed to accept erase so you could have an array in that argument but perhaps this function is programmed to not allow arrays there at least not in this circumstance both circle chicken or knife you know posted questions to find some of the smarter Excel people at the message board saying hey what's going on with this why what is it about index that doesn't allow this all right so I'll leave that there because that's a at least an intelligent try but here's what circle chicken did he went ahead and used those two matches and we had to add two because we're two rows down and then he put it inside the address function which will give us the addresses and I'm gonna build this in just a second and then he took the indirect of address because address gives us a cell reference as text and that gave us all the individual cell references and then there was a problem with that but if you put it inside a transpose and then some it works so let's build this one step at a time we're gonna use our two matches and we're just gonna look at the first match alright so looking back here I'm gonna say he put throw all these in there comma and then look up array call my zero and let's evaluate this because really we want all the row numbers so for we should get a 1 a 2 a 2 a 2 so a 1 a 2 a 2 a 2 looks like 4 and a 5 and when I f9 this sure enough it does work so the match function control-z can handle that but and I'm gonna enter this just for a moment but some for some reason we did that over here and the row number it's just the index didn't pick it up so no problem I'm gonna now use the address function now the address function is cool if you'd give it a row number and a column number it will give you the address right so check this out again f9 that's giving me all the row numbers that's in the row argument I'm simply going to do the same thing up here for this this will give me all the column numbers and the address will spit out a bunch of cell references alright so let's do this this is exciting because this is an example of where we have these two matches with array the address can handle it where where as it looks like the index wasn't able to handle it all right so now I got all of my Department numbers here comma within this range comma zero right and just to jump to the chase let's check this out f9 oh it's so cool the address does what it's supposed to it delivers cell references but as text and we'll fix that in just a moment using the indirect control Z I just want to check this out I'm going to enter this and then I'm going to do this little trick where you go like this f9 + f9 because this is I mean in terms of the REA form is that's pretty beautiful if we're throwing in an array array and it's given me this the address for Row 1 column 2 row 2 column 1 Row 2 column 3 etc now I'm going to click escape because I if u f9 two times in a row it hard codes them in all right now address as we saw spits this out as text but no problem there's a function specifically built to take cell references as text and convert them back to cell references that's what the indirect does and you can highlight this and hit the f9 ok so now we see a problem it's actually not working because what that's 1 in our array but the address needs one two three control-z so what are we going to do we're gonna plus 2 right because it address absolutely needs to actual row numbers 1 2 3 so it needs 3 for this whereas the match just looked at that and threw out a 1 alright so you could do plus 2 and over here we need one more so I'm going to plus 1 and so now when they highlight this and hit the f9 key that is absolutely magic now here's where it gets a little bit on you know immediately I thought well I can see those numbers unjust throwing that in some product control Z or some and control shift enter right and so I hit enter and it's not working and again both circle chicken and I Paul and circle chicken actually posted a bunch of cool alternative form as with different functions and the bottom line is sort of a circle is saying that you have to have some other function wrapped around the int this whole piece right here and it could be anything it could be some or some product or transpose as he used any function alright so we're just gonna put some right I chose some product on the outside because then it I don't have to use control shift enter and so I hit enter and boom now a circle chicken actually went like this some and then transpose whoops and then he had to use control shift enter because the Sun was on the outside I'm gonna ctrl Z I also had one other incarnation I use the end function the end function and actually these little screen tips in 2010 are awesome converts an on number value to a number so I just used the end function and then enter now whoops whoa now I did one last little thing instead of having this Plus tool I extended the range so notice it's a three to a seven so I just changed this to a one alright so now it's actually looking through that range and that's why I put a zero there right because then it's gonna look at a blanket account but now it's looking for an exact value and same over here instead of the plus one I put a two so right now so what's going from there to there and so now it's the matches will actually deliver the correct row and column number for the address in either case if you were to insert a row up here and push this down either this setup right here or the adding the two the one would then give you an incorrect answer but for this setup right here enter our works just fine all right totally amazing solution here posted by a circle chicken two different formulas there we'll see you next trip

Channel: ExcelIsFun

Views: 25,502

Rating: 4.8446603 out of 5

Keywords: Excel, 2007, 2010, excelisfun, Mike, Gel, Girvin, Highline, Community, College, Slaying, Dragons, Multiple, Two, Way, Lookup, Adding, using, INDIRECT, ADDRESS, ARRAY, MATCH, Function, as, well, SUMPRODUCT, SUM, and, functions, Amazing, trick, from, circlechicken, at, Mr, Message, Board, Trial, Balance, Accounting, Report

Id: VE4AKPuJiis

Length: 11min 16sec (676 seconds)

Published: Tue Sep 27 2011