MEUserII
Board Regular
- Joined
- Oct 27, 2017
- Messages
- 91
- Office Version
- 365
- 2021
- 2019
- 2016
- 2013
- Platform
- Windows
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:
<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
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