Vlookup Formula Limit Error ? (Reposting)
 

Hi all,

Given below is a formula that we are trying to build,

IF($I$4INDEX(CategoryLookup!F:G,MATCH(VLOOKUP(IND IRECT(IF(ISBLANK($N$4),"$D$4","$N$4")),CategoryLoo kup!A:C,2,0),CategoryLookup!E:E,0),2),
"Above",IF($I$4<INDEX(CategoryLookup!F:G,MATCH(VLO OKUP(INDIRECT(IF(ISBLANK($N$4),"$D$4","$N$4")),Cat egoryLookup!A:C,2,0),CategoryLookup!E:E,0),1),
"Below",PERCENTRANK(INDIRECT(CONCATENATE("Category Lookup!F",MATCH(VLOOKUP(INDIRECT(IF(ISBLANK($N$4), "$D$4","$N$4")),CategoryLookup!A:C,2,0),CategoryLo okup!E:E,0),":","G",MATCH(VLOOKUP($D$4,CategoryLoo kup!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


--
xcelion
------------------------------------------------------------------------
xcelion's Profile: http://www.excelforum.com/member.php...o&userid=16287
View this thread: http://www.excelforum.com/showthread...hreadid=393210

Put INDIRECT(IF(ISBLANK($N$4),"$D$4","$N$4")) into a separate cell then
refer to its result in your target formula.

Also, I don't think you need to use INDIRECT. Instead try
IF(ISBLANK($N$4),$D$4,$N$4)

You might also review your use of INDIRECT in the rest of the formula.

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"xcelion" wrote in
message ...

Hi all,

Given below is a formula that we are trying to build,


IF($I$4INDEX(CategoryLookup!F:G,MATCH(VLOOKUP(IND IRECT(IF(ISBLANK($N$4),"$D
$4","$N$4")),CategoryLookup!A:C,2,0),CategoryLooku p!E:E,0),2),

"Above",IF($I$4<INDEX(CategoryLookup!F:G,MATCH(VLO OKUP(INDIRECT(IF(ISBLANK($
N$4),"$D$4","$N$4")),CategoryLookup!A:C,2,0),Categ oryLookup!E:E,0),1),

"Below",PERCENTRANK(INDIRECT(CONCATENATE("Category Lookup!F",MATCH(VLOOKUP(IN
DIRECT(IF(ISBLANK($N$4),"$D$4","$N$4")),CategoryLo okup!A:C,2,0),CategoryLook
up!E:E,0),":","G",MATCH(VLOOKUP($D$4,CategoryLooku p!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


--
xcelion
------------------------------------------------------------------------
xcelion's Profile:
http://www.excelforum.com/member.php...o&userid=16287
View this thread: http://www.excelforum.com/showthread...hreadid=393210

Sorry, should also have said that it looks as though you've reached the
function nesting level limit. In Help look for "About nesting functions
within functions".

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"Andy Wiggins" wrote in message
...
Put INDIRECT(IF(ISBLANK($N$4),"$D$4","$N$4")) into a separate cell then
refer to its result in your target formula.

Also, I don't think you need to use INDIRECT. Instead try
IF(ISBLANK($N$4),$D$4,$N$4)

You might also review your use of INDIRECT in the rest of the formula.

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"xcelion" wrote in
message ...

Hi all,

Given below is a formula that we are trying to build,



IF($I$4INDEX(CategoryLookup!F:G,MATCH(VLOOKUP(IND IRECT(IF(ISBLANK($N$4),"$D
$4","$N$4")),CategoryLookup!A:C,2,0),CategoryLooku p!E:E,0),2),


"Above",IF($I$4<INDEX(CategoryLookup!F:G,MATCH(VLO OKUP(INDIRECT(IF(ISBLANK($
N$4),"$D$4","$N$4")),CategoryLookup!A:C,2,0),Categ oryLookup!E:E,0),1),


"Below",PERCENTRANK(INDIRECT(CONCATENATE("Category Lookup!F",MATCH(VLOOKUP(IN

DIRECT(IF(ISBLANK($N$4),"$D$4","$N$4")),CategoryLo okup!A:C,2,0),CategoryLook

up!E:E,0),":","G",MATCH(VLOOKUP($D$4,CategoryLooku p!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


--
xcelion
------------------------------------------------------------------------
xcelion's Profile:
http://www.excelforum.com/member.php...o&userid=16287
View this thread:
http://www.excelforum.com/showthread...hreadid=393210

Thanks Andy.Thanks for your help
]


--
xcelion
------------------------------------------------------------------------
xcelion's Profile: http://www.excelforum.com/member.php...o&userid=16287
View this thread: http://www.excelforum.com/showthread...hreadid=393210