Dueling Excel
Bill Mr. Excel Jelen Vs.Mike Gel Girvin Excel is Fun
Bill Jelen: Hey I’m Bill Jelen from Mr. Excel.com and I’ve got a cool excel tip for you today.
Mike Gel Girvin: Hey this Mike Gel Girvin at Excel is Fun in YouTube and I have a different way to do that.
2-Way Lookup
Bill Jelen: Hey welcome back it’s another dueling excel podcast. I’m Bill Jelen from Mr. Excel will be joined by Mike Girvin from Excel is fun. Mike all this week at Mr. Excel we’ve been doing VLOOKUP week talking about different VLOOKUP issues. Now I want to do the two-way lookup so we have this table here with account numbers down the side and months going across the top based on these two cells I want to find the value at the inner section.
Alright so I want to build in three pieces equal match. Go find this value A one, one, four. I will press F4, in the left hand column of this table CTRL SHIFT down arrow press F4 and then finally we want an exact match comma zero here is just like comma false in the VLOOKUP. It says that’s on row 14 right now it matches really cool because it go both vertically or horizontally so for the column.
Go look for February within the first row of the table press F4 again and again comma zero and that says hey it’s in column two. Well what good is that well now we’re going to use this index function and the index is going to take our whole array mixture of leave the headings out so we select D10 to E 233 press F4 and what do we want. Well we want this particular row and then this particular column and there is our result 0.352.
Let’s just check it A114 there it is 0.352 and it will enter something different here it’s like A104 0.319 there it is its working. Okay now that I have a working check this out. This is my cool, cool trick. See when we refer to cell B5 here. I’m going to go back to B5, I’m going to go up to the formula bar and copy everything except for the equal sign control C to copy and come back here and click on B5 and control V to paste. Cool Alright.
Next up B6, come back to B6 I click on everything except for the equal sign Control C to copy come back down here and B6 to paste isn’t that cool. Control V Enter and now I have my formula I can control X and bring it back up here to the answer and I know longer need these rows here, we can just edit, delete the entire row. There gone and everything is working. Index with a couple of matches, alright now, let’s throw it over to Miek and see what Mike has.
Mike Gel Girvin: Thanks Mr. Excel, whoa de ja vu you know this is 32nd duel and all the way back 32 weeks ago dual number one we did this two way look up. Now index match, match, we did that the last time and we did an intersector operator. Now I’m going to show you a different method and I want to compare it to index match, match.
Notice the index has the guts of the table and then index just needs a row number and a column number, but what about VLOOKUP. We could use VLOOKUP because VLOOKUP always looks in the first column and then we tell it what— I’m sorry the first column it finds a row jumps over and we tell it what column and then it selects that value.
Well instead of typing a column reference in we can use the match inside the VLOOKUP so let’s see how that works, equals VLOOKUP. I’m going to say the LOOKUP value since we want to find an accounting in the first column that’s the way VLOOKUP works because I’m going to select that, the table is going to be not including the headers up here. That range control shift down arrow, that’s the table array, and then the column index.
Now one, two, three, four we put a four there for March but the way we can’t hard quote it in because we want it volatile so we use the match. The mach I would say hey look up that comma within this range right here comma and then exact match and the match will deliver the ordinal position one, two, three, four.
Match inside the column index argument inside a VLOOKUP. We don’t need the last argument because this is sorted ascending so we just close parenthesis and then control Enter so that will work right there VLOOKUP and match if I type say January.
Now 32 weeks ago what I did was the intersect operator and I’m going to remind you of that here because it’s a, I don’t think it says efficient as these two methods but it’s interesting this is a good one you drop on your friend that thinks they know everything about excel and you go what about the interesector operator.
Now, intersector operator works like this, we highlight some range type a space, that’s the intersector operator. And we highlight another range that intersect and it will always return the intersection. Now you don’t want to have to do that each time. We want it linked to these up here. Now how do you name because that means we would have to name this whole column January and this one whole February, this accounting, this one accounting to 103 etcetera.
Well there’s a keyboard that does this automatically. I’m going to select the whole table. This is the selection keyboard trick, CTRL * and then the keyboard shortcut to name from selection is CTRL SHIFT F3. Let’s going say hey our names in the top row in the left column yes they are.
Now let’s go ahead and try it equals J a N space accounting 104. Sure not fail to return that 357. Now we don’t have to type it in each time so we need a formula that’s actually linked to these cells. The problem with this is that Excel sees text that’s text JEN is considered text so that’s not going to work there’s no intersection. So instead of using the raw cell reference you have to use the indirect function which takes text like JEN and converts it to its reference.
That is a name in memory so it does convert to a reference space and then indirect again and we click right there. Oh so that’s a very obscure way to do a two-way lookup but sure enough it will work if I type in March here then they all update. All right we’ll see you next trip.
Bill Jelen: Oh my gosh Mike you got to be kidding me, you can type in equal January space accounting 101. I always tell you to put that in the sum function, that’s incredible like I couldn’t believe it worked. I had to come here and test it out for myself there you go you just dropped on me. Alright Mike thanks for that great tip and thanks to everyone else who stopping. We’ll see you next time for another dueling excel podcast from Mr. Excel and Excel is Fun.
Transcription by:
Scribe4you Transcription Services