How to Nest Excel Worksheet Functions in a Formula

What is function nesting and why use it?

When calculations involving multiple operations have to be performed in a formula, the need will arise to nest Excel functions. 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.

Scroll < > picture if hidden.

The sample worksheet above demonstrates nesting a function call within another function call in a formula. The RIGHT worksheet function returns the SN from the 4 right-hand side characters of the Product ID. That value is then used by the VLOOKUP to find the part SN in the second table and return the weight of the part. The *1 after RIGHT flips its return value from text to a number because VLOOKUP needs a number if it is looking in a table whose first column is a number.

How to Build Nested Functions in a Formula

To nest a function within another function in a formula:

First build the syntax that runs the functions to be used in separate formulas. Place static values in place of where the nesting will be used. This allows each function formula to be tested without the hassle of a nested structure.
In the formula where the nesting will take place, put it into edit mode (F2 or double click on cell) and place an apostrophe in front of the equal sign. Press Enter. The apostrophe turns the formula into a piece of text so it keeps it from calculating. This way if you have multiple nestings, you can do it piece meal and not have the formula error out during construction.
Double click on the function formula to be nested. Highlight the syntax for the function call only in the formula and press Ctrl+C. This copies the syntax as text. Do not include anything around it. Press the Esc key to escape the formula without damaging it.
Double click on the formula to nest in. Highlight the static value in the formula that is the place holder for where you want to nest and paste the syntax over it. You can put spaces around the value before pasting as this will not hurt the formula and allows a better visual ID of the place holder to be pasted over.
After pasting, remove the apostrophe from the front of the formula and press Enter to calculate. Makes sure the formula is calculating correctly.

Scroll < > picture if hidden.

The sample worksheet above demonstrates a simple method to build and test nested functions within an Excel formula. The idea is simple, build and test parts of the nested formula then combine the parts.

Additional Tutorial Topics: