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:

ROW/COLUMN

A

B

C

D

E

F

G

H

I

J

K

J

1

ALPHA

BRAVO

CHARLIE

DELTA

ECHO

FOXTROT

GOLF

HOTEL

INDIA

JULIETT

KEY

FORMULA

2

FRUIT1

VEGETABLE1

VEGETABLE4

MEAT1

MEAT5

MEAT9

DESSERT1

DESSERT6

DESSERT11

DESSERT16

1/2/3/4

=FORMULA

3

FRUIT2

VEGETABLE2

VEGETABLE5

MEAT2

MEAT6

MEAT10

DESSERT2

DESSERT7

DESSERT12

DESSERT17

4

VEGETABLE3

VEGETABLE6

MEAT3

MEAT7

MEAT11

DESSERT3

DESSERT8

DESSERT13

DESSERT18

5

MEAT4

MEAT8

MEAT12

DESSERT4

DESSERT9

DESSERT14

DESSERT19

6

DESSERT5

DESSERT10

DESSERT15

DESSERT20

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]

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)