Excel 2024: Use Function Arguments for Nested Functions
June 24, 2024 - by Bill Jelen
The Function Arguments dialog shown above is cool, but in real life, when you have to nest functions, how would you use this dialog?
Say that you want to build a formula to do a two-way lookup:
=INDEX(B2:E16,MATCH(G2,A2:A16,0),MATCH(H2,B1:E1,0))
You would start out using the Function Arguments dialog box for INDEX
. In the Row_num argument box, type MATCH(
. Using the mouse, go up to the formula bar and click anywhere inside the word MATCH
.
Caution: Don't click the formula in the cell. You have to click the formula in the formula bar.
The Function Arguments dialog switches over to MATCH
. When you are finished building the MATCH
function, go up to the formula bar and click anywhere in the word INDEX
.
Repeat these steps to build the MATCH
in the third argument of INDEX
. Make sure to click back in the word INDEX
in the formula bar when you are done with the second MATCH
.
It turns out that the Function Arguments dialog can be fooled into building an invalid function. Type a well-formed but nonsensical function in the formula bar. Using the mouse, click inside the fake function name in the formula bar and click the fx
icon.
Thanks to Tony DeJonker, Cat Parkinson, & Geoff in Huntsville for suggesting the Function Arguments dialog trick.
This article is an excerpt from MrExcel 2024 Igniting Excel
Title photo by Abhijit Sinha on Unsplash