Excel - How to Make a Nested Formula in Excel using Function Arguments dialog - Episode 2073

Показать описание

Microsoft Excel Tutorial: How to nest functions in the Functions Arguments dialog

Welcome to the MrExcel netcast, where we bring you the latest tips and tricks for mastering Excel. In this episode, we will be discussing the Nested Function Arguments Dialog and how to use it to your advantage. I'm Bill Jelen and I'm excited to share this valuable information with you.

During one of my seminars in Huntsville, Alabama, a participant named Jeff in the front row helped us solve a problem we were struggling with. We were discussing the use of Function Arguments and someone asked how to use two different functions, like Nested MATCH inside of INDEX. This sparked our curiosity and we decided to dive deeper into this topic.

Let's start with a simple example. We have a table with account numbers on the left and months across the top. Our goal is to find the intersection of a chosen account number and month. This can easily be achieved with the INDEX function, but how do we build this formula using Function Arguments? We start by selecting the INDEX function and clicking OK, which takes us to the Function Arguments Dialog Box.

Here's where things get interesting. We need to use the MATCH function to find the row and column numbers for our INDEX function. To insert the MATCH function into the argument, we use the Name Box to the left of the formula bar. If the MATCH function is not already there, we can simply go to More Functions and type in MATCH. Once we have the MATCH function in the argument, we can continue building our formula in the Function Arguments Dialog Box.

But here's the cool part - once we are done with the interior function (in this case, the MATCH function), we can simply click on the original function in the formula bar (in this case, INDEX) and it will take us back to the Function Arguments Dialog Box. This allows us to easily nest functions and build complex formulas without getting lost in the process.

I hope this tip has been helpful and has opened up new possibilities for you in Excel. For more tips and tricks, be sure to check out my book, Power Excel with MrExcel, the 2017 Edition, where I share 617 Excel mysteries solved. Thank you for tuning in to this episode of the MrExcel netcast and I'll see you next time for more Excel insights.

#excel
#microsoft
#microsoftexcel
#exceltips
#exceltricks
#excelhacks
#excelformula
#excelformulasandfunctions
#evergreen

This video answers these common search terms:
what is function arguments in excel
where is function arguments dialog for excel
how to use function arguments excel
how to make a nested formula in excel
nested functions how to excel
what is a nested function excel
how to open function arguments in excel
how to nest a formula in excel
what is a nested formula in excel
excel how to nest formulas
how to create nested formulas in excel
how to use nested if formula in excel
how to create a nested formula excel
how to insert a nested formula in excel
how to write a nested if and formula in excel
how to create nested functions in excel
how to make a nested function in excel
how to use a nested if function in excel
how to write nested if function in excel
how to nest multiple left functions in excel
how arguments in the and function of excel
how to see arguments of function in excel
how to find arguments of function in excel

Table of Contents:
(00:00) Nested Function Arguments
(00:23) Using Function Arguments for the Insert Function dialog
(00:38) Building a formula using Function Arguments
(01:17) Using the Name Box to insert functions
(01:39) Completing the formula with multiple MATCH functions
(02:07) Unintuitive process for nesting functions in Function Arguments
(02:44) Successful formula using Function Arguments
(03:02) Clicking Like really helps the algorithm

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