Nested INDEX Functions to Select INDEX ISBLANK-Wrapped Reference Range Using Area_Num Parameter?

MEUserII

Board Regular
Joined
Oct 27, 2017
Messages
59
I am trying to use the INDEX function to select a reference range created by another INDEX function selected by the "Area_Num" parameter where that other INDEX function refers to an ISBLANK-wrapped reference range. For reference, I have the following Excel formula in Excel 2016 on Windows 10:

Excel Formula inputted in J2:
=MATCH( (TRUE), (INDEX( ( (INDEX( (ISBLANK($A$1:$A$7) ), (0), (0) ) ), (INDEX( (ISBLANK($B$1:$C$7) ), (0), (0) ) ), (INDEX( (ISBLANK($D$1:$F$7) ), (0), (0) ) ), (INDEX( (ISBLANK($G$1:$J$7) ), (0), (0) ) ) ), (0), (0), ($K$2) ) ), (0) )

Data:
ROW/COLUMNABCDEFGHIJKJ
1ALPHABRAVOCHARLIEDELTAECHOFOXTROTGOLFHOTELINDIAJULIETTKEYFORMULA
2FRUIT1VEGETABLE1VEGETABLE4MEAT1MEAT5MEAT9DESSERT1DESSERT6DESSERT11DESSERT161/2/3/4=FORMULA
3FRUIT2VEGETABLE2VEGETABLE5MEAT2MEAT6MEAT10DESSERT2DESSERT7DESSERT12DESSERT17
4VEGETABLE3VEGETABLE6MEAT3MEAT7MEAT11DESSERT3DESSERT8DESSERT13DESSERT18
5MEAT4MEAT8MEAT12DESSERT4DESSERT9DESSERT14DESSERT19
6DESSERT5DESSERT10DESSERT15DESSERT20
7
8

<tbody>
</tbody>

Where, "K2" can be either: 1, 2, 3, or 4; to reflect the input of the "Area_Num" parameter which the "outer" INDEX function is supposed to use to select which reference range to be used; either reference range: "$A$1:$A$7", "$B$1:$C$7", "$D$1:$F$7", or "$G$1:$J$7"; where each of these respective ranges are wrapped in an INDEX( (ISBLANK(RNG)), (0), (0) ) to generate the range of "ISBLANK cells (in TRUE/FALSE format)" for the MATCH function to match to "TRUE" and return the exact position of that match for the respective range selected by the "Area_Num" parameter.

However, the above main formula mentioned keeps returning a "#VALUE!" error, so I am stuck on how to fix this error?
Would anyone know how to fix this error/fix this formula?

For reference, documentation on "Area_Num" paramater as well as using the INDEX function to return a "wrapped range" as in "INDEX( (ISBLANK(RNG) ), (0), (0) )" is listed in the Microsoft Office documentation for the INDEX function linked below.

Link: https://support.office.com/en-us/article/INDEX-function-A5DCF0DD-996D-40A4-A822-B56B061328BD
 

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
532
Office Version
365, 2013
Platform
Windows
Hi,

Not sure if I understood your request correctly - would you like to return the first row in a given range that is blank? Taking your sample data as an example, for "$A$1:$A$7" you'd like to see 4, "$B$1:$C$7" = 5, "$D$1:$F$7" = 6, and for "$G$1:$J$7" = 7?

If yes, try using the following formula in cell L2:
=MATCH(TRUE,CHOOSE(K2,INDEX(ISBLANK($A$1:$A$7),0,1),INDEX(ISBLANK($B$1:$C$7),0,1),INDEX(ISBLANK($D$1:$F$7),0,1),INDEX(ISBLANK($G$1:$J$7),0,1)),0)
 

Forum statistics

Threads
1,085,165
Messages
5,382,080
Members
401,768
Latest member
katana_flyer

Some videos you may like

This Week's Hot Topics

Top