MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Nested Function Arguments Dialog

April 11, 2017 - by Bill Jelen

Nested Function Arguments Dialog

The fx button in Excel helps you to build a function, one argument at a time. But what happens when you have to nest one function inside of another function? There is a way to use the Function Arguments for each of the nested functions.

Watch Video

  • How to nest functions in the Functions Arguments dialog
  • Start using fx
  • When you need to insert a function in an argument, use the Name Box
  • The Name Box is to the left of the formula bar
  • When you finish the nested function, click on the Original Function, in the formula bar not in the cell

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2073 nested function arguments dialogue
  • 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 solve the whole thing for us
  • and I was talking about using function
  • arguments right the insert function here
  • but someone said we'll wait we've tried
  • use insert function boot if we have to
  • use two different functions like nesta
  • match inside of it index how would you
  • ever do that all right so we're starting
  • 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 of those and this
  • is the formula that will do that but how
  • would we build that formula using
  • function arrogance so we come here and
  • we say that we're trying to start with
  • an index function and click OK which now
  • takes us into the function Agra so the
  • one we want this is weird here that
  • there's two different ways how we reuse
  • this first one with a railroad 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 all right so here's the
  • array of possible answers and then we
  • click tab to move down to the road
  • number and this is where I want that
  • first match to be okay so because I want
  • to 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 matches
  • there because I just tested this a
  • minute ago but for most cases you go to
  • more functions and type match click go
  • and then click OK all right now we're
  • building the match all right so go look
  • for see 236 tab in this list of accounts
  • tab and the matched up we want is an
  • exact match which is zero 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
  • huh 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
  • 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 but we just
  • used it a few seconds ago the lookup
  • value is februari lookup array is this
  • list across the top and tab match type
  • of 0 right now we're actually done and I
  • dealt me 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 c230 64
  • februari is 9 awesome and let's just do
  • it another test here April of e 1 06 e
  • 106 right there april twenty 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 the 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 here on
  • the form of stab doesn't matter either
  • one then when you need to 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 form of the bar
  • up there i click on index and it will
  • take you back to that function arguments
  • a cool cool trick ok i wanna thank you
  • for stopping by we'll see you next time
  • for another NetCast from MrExcel

Download File

Download the sample file here: Podcast2073.xlsm

Title Photo: Mary Ellen Jelen / We Report Space

Bill Jelen is the author / co-author of
Excel Subtotals Straight to the Point

I used to use the Subtotals feature daily after downloading mainframe data. This book covers every tip and trick for using Subtotals.