How to Nest Microsoft Excel Worksheet Functions

Discussed below is how to nest a function inside of another function.

Nesting Microsoft Excel Worksheet functions in a formula is the key to developing advanced formulas for dashboards, models and data processing tools in Excel.

Excel Worksheet Function Nesting Overview

When sophisticated calculations have to be performed in a worksheet cell, the need will arise to nest Excel functions in a single formula. This saves worksheet space as only one cell is used to do multiple operations. The simple concept behind nesting functions is to place the function syntax that runs the function in the argument list of another function in the same formula. This allows the inside function to calculate first then feed its result value to the function whose argument list it was placed in.

For example, say you are trying to use the VLOOKUP function to lookup a value in a table on a worksheet. In the first column of the worksheet table, there is a list of dates that you will look up. Dates on a worksheet are really stored as numbers like 1/1/2017 = 42736 as discussed in all of our Excel classes. If you tried to plug 1/20/2017 into the first argument of the VLOOKUP function, you would error out. That is because 1/20/2017 is not really a date, it is 1 divided by 20 divided by 2017 because there are no real dates in Excel. So you need a function that can flip the date you are looking for into the number that is really stored on the worksheet in the first column of the table. To do this you could nest DATEVALUE() as the first argument of VLOOKUP and it would flip the date syntax to a number that VLOOKUP could then use. This concept is illustrated in the formula below.

=VLOOKUP( DATEVALUE( "1/20/2017" ) , A1:D100 , 2 , FALSE )