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 should
calculate the values for $D$4 and $N$4 depending on which field has
value (non blanks). So we tried to insert the condition
INDIRECT(IF(ISBLANK($N$4),"$D$4","$N$4")). This worked fine for the
first and second replacement of $D$4. But when we tried to replace it
for the next occurance, as shown underlined, excel didn't allow to
enter the formula. Can anyone help on this ?
Is it some built in restriction on the usage of IF,VLOOKUP or INDIRECT
?
Thanks in advance
Xcelion
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 should
calculate the values for $D$4 and $N$4 depending on which field has
value (non blanks). So we tried to insert the condition
INDIRECT(IF(ISBLANK($N$4),"$D$4","$N$4")). This worked fine for the
first and second replacement of $D$4. But when we tried to replace it
for the next occurance, as shown underlined, excel didn't allow to
enter the formula. Can anyone help on this ?
Is it some built in restriction on the usage of IF,VLOOKUP or INDIRECT
?
Thanks in advance
Xcelion