Vlookup Formula Limit Error ? (Reposting)

  • Thread starter Thread starter xcelion
  • Start date Start date
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
 
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.
 
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 said:
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 said:
Hi all,

Given below is a formula that we are trying to build,
Click to expand...
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),
Click to expand...
"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),
Click to expand...
"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!
Click to expand...
 
Back
Top