X
xcelion
Hi all,
Given below is a formula that we are trying to build,
IF($I$4>INDEX(CategoryLookup!F:G,MATCH(VLOOKUP(*INDIRECT(IF(ISBLANK($N$4),"$D$4","$N$4"))*,CategoryLookup!A:C,2,0),CategoryLookup!E:E,0),2),"Above",IF($I$4<INDEX(CategoryLookup!F:G,MATCH(VLOOKUP(*INDIRECT(IF(ISBLANK($N$4),"$D$4","$N$4"))*,CategoryLookup!A:C,2,0),CategoryLookup!E:E,0),1),"Below",PERCENTRANK(INDIRECT(CONCATENATE("CategoryLookup!F",MATCH(VLOOKUP(_*INDIRECT(IF(ISBLANK($N$4),\"$D$4\",\"$N$4\"))*_,CategoryLookup!A:C,2,0),CategoryLookup!E:E,0),":","G",MATCH(VLOOKUP(*$D$4*,CategoryLookup!A:C,2,0),CategoryLookup!E:E,0))),$I$4,2)))
We are trying to build a formula. As given above, the formula shoul
calculate the values for *$D$4* and *$N$4* depending on which field ha
value (non blanks). So we tried to insert the conditio
*INDIRECT(IF(ISBLANK($N$4),"$D$4","$N$4"))*. This worked fine for th
first and second replacement of *$D$4*. But when we tried to replace i
for the next occurance, as shown underlined, excel didn't allow to ente
the formula. Can anyone help on this ?
Is it some built in restriction on the usage of IF,VLOOKUP or INDIREC
?
Thanks in advance
Xcelio
Given below is a formula that we are trying to build,
IF($I$4>INDEX(CategoryLookup!F:G,MATCH(VLOOKUP(*INDIRECT(IF(ISBLANK($N$4),"$D$4","$N$4"))*,CategoryLookup!A:C,2,0),CategoryLookup!E:E,0),2),"Above",IF($I$4<INDEX(CategoryLookup!F:G,MATCH(VLOOKUP(*INDIRECT(IF(ISBLANK($N$4),"$D$4","$N$4"))*,CategoryLookup!A:C,2,0),CategoryLookup!E:E,0),1),"Below",PERCENTRANK(INDIRECT(CONCATENATE("CategoryLookup!F",MATCH(VLOOKUP(_*INDIRECT(IF(ISBLANK($N$4),\"$D$4\",\"$N$4\"))*_,CategoryLookup!A:C,2,0),CategoryLookup!E:E,0),":","G",MATCH(VLOOKUP(*$D$4*,CategoryLookup!A:C,2,0),CategoryLookup!E:E,0))),$I$4,2)))
We are trying to build a formula. As given above, the formula shoul
calculate the values for *$D$4* and *$N$4* depending on which field ha
value (non blanks). So we tried to insert the conditio
*INDIRECT(IF(ISBLANK($N$4),"$D$4","$N$4"))*. This worked fine for th
first and second replacement of *$D$4*. But when we tried to replace i
for the next occurance, as shown underlined, excel didn't allow to ente
the formula. Can anyone help on this ?
Is it some built in restriction on the usage of IF,VLOOKUP or INDIREC
?
Thanks in advance
Xcelio