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

[MEUserII]

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:
[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD="align: center"]ROW/COLUMN[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]ALPHA[/TD]
[TD="align: center"]BRAVO[/TD]
[TD="align: center"]CHARLIE[/TD]
[TD="align: center"]DELTA[/TD]
[TD="align: center"]ECHO[/TD]
[TD="align: center"]FOXTROT[/TD]
[TD="align: center"]GOLF[/TD]
[TD="align: center"]HOTEL[/TD]
[TD="align: center"]INDIA[/TD]
[TD="align: center"]JULIETT[/TD]
[TD="align: center"]KEY[/TD]
[TD="align: center"]FORMULA[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]FRUIT1[/TD]
[TD]VEGETABLE1[/TD]
[TD]VEGETABLE4[/TD]
[TD]MEAT1[/TD]
[TD]MEAT5[/TD]
[TD]MEAT9[/TD]
[TD]DESSERT1[/TD]
[TD]DESSERT6[/TD]
[TD]DESSERT11[/TD]
[TD]DESSERT16[/TD]
[TD]1/2/3/4[/TD]
[TD]=FORMULA[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]FRUIT2[/TD]
[TD]VEGETABLE2[/TD]
[TD]VEGETABLE5[/TD]
[TD]MEAT2[/TD]
[TD]MEAT6[/TD]
[TD]MEAT10[/TD]
[TD]DESSERT2[/TD]
[TD]DESSERT7[/TD]
[TD]DESSERT12[/TD]
[TD]DESSERT17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD][/TD]
[TD]VEGETABLE3[/TD]
[TD]VEGETABLE6[/TD]
[TD]MEAT3[/TD]
[TD]MEAT7[/TD]
[TD]MEAT11[/TD]
[TD]DESSERT3[/TD]
[TD]DESSERT8[/TD]
[TD]DESSERT13[/TD]
[TD]DESSERT18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"] 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MEAT4[/TD]
[TD]MEAT8[/TD]
[TD]MEAT12[/TD]
[TD]DESSERT4[/TD]
[TD]DESSERT9[/TD]
[TD]DESSERT14[/TD]
[TD]DESSERT19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DESSERT5[/TD]
[TD]DESSERT10[/TD]
[TD]DESSERT15[/TD]
[TD]DESSERT20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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]

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)