Nested Function Arguments Dialog

Learn Excel from MrExcel Podcast, Episode 2073: Nested Function Arguments Dialog

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.  Yet, another question that came up in my Huntsville, Alabama seminar and we actually did a horrible job of explaining this in the seminar until someone named Jeff in the front row solved the whole thing for us.  And I was talking about using Function Arguments, right, the INSERT function here.  But someone said, “Well wait, we've tried to use INSERT function but if we have to use two different functions like Nested MATCH inside of INDEX, how would you ever do that?”

Alright, so we're going to start here.  First, you know, we get to choose an Account Number from the left-hand side, choose a Month from across the top, and find the intersection to those.  And this is the formula that will do that.  But how would we build that formula using Function Arguments?  So, we come here and we said that we're trying to start with an INDEX function and click OK, which now takes us into the Function Arguments.  So the one we want, this is weird here that there's different ways.  How will we use this first one with array, row number, column number?  And we're now in Function Arguments for the INDEX function.  And you see, we're building the formula up here and here as well.  Alright, so here's the array of possible answers.  And then we click Tab to move down to the row number and this is where I want that first match to be, okay?  So, because I want a MATCH to go here, I'm going to open the Name Box, that's the thing to the left of the formula bar, and now MATCH is there because I just tested this a minute ago.  But for most cases you go to More Functions and type MATCH, and click Go, and then click OK.  Alright now, we're building the MATCH.  Alright, so go look for C236, Tab, in this list of accounts Tab and the MATCH that we want is an exact MATCH which is 0.  Alright, and now we're done with this MATCH but don't click OK, that will try and accept the whole formula which will be a disaster.  All you have to do is come here in the formula bar and click on INDEX, like that.  Ahh, see, and that's going to take us back to where we were building the first one.  How cool is that.  Completely unintuitive, we tried seven other things before we get this one to work.  Alright, and here we want the other MATCH, the second MATCH, the MATCH for the month.  So now that I'm in this one, open the Name Box and MATCH, sure enough will be one of the most recent functions, we just used it a few seconds ago.  The Lookup value is February, Lookup array is this list across the top and Tab, Match type of 0.  Alright, now we're actually done and I don’t need to go back to the INDEX function because the INDEX function is now done.  So I can simply click OK and that will accept the whole thing: C236 for February is 9.  Awesome. 

And let's just do another test here: April of E106, E106 right there.  April 20, it is working.  And it was all built using the INSERT function and the Function Arguments Dialog Box. 

For this tip and a lot of other tips, check out my new book, Power Excel with MrExcel, the 2017 Edition, 617 Excel mystery solved.

Alright, so the question was how to nest functions in the Function Arguments dialog box.  We start off using a little fx button here to the left of the formula bar or heck, that one out there on the Formulas tab, doesn't matter either one.  Ahh, then when you need it, insert a function into an argument, use the Name Box over here and then when you're done with that interior function, click on the Original Function in the formula bar right up there, and click on INDEX and it will take you back to that Function Arguments.  A cool, cool trick. 

Hey, I want to thank you for stopping by.  We'll see you next time for another netcast from MrExcel.