Vlookup reference error

[jhogie]

I will try to describe everything the best I can. The following are in my excel:

X11: can be "Remove From Program" or any positive integer (in this case it is 22)
AC11: 1, 2, 3 or 4 (in this case it is 4)

Row of cells starting at AE3:
1 2 3 4 5

Table array starting with cell AD4 ('>20'):
>20 IS-4 IS-3 IS-2 IS-1 IS-1
17-20 IS-4 IS-4 IS-3 IS-2 IS-1
1-16 IS-4 IS-4 IS-4 IS-3 IS-2

Code in AJ11:
=IF(X11="Remove From Program","Remove From Program",IF(X11>20,VLOOKUP($AD$4, table,(AC11+1),FALSE)))


When X11 = "Remove from Program" it works fine! When X11 = any number it returns "#REF!". It is suppose to return "IS-1". The frustrating part is the EXACT same tables in other excel files work properly.

Any help would be much appreciated. Thanks in advance,

- Hogie

 

[SkipVought]

HI,

Select any value or expression in the FORMUAL BAR and hit F9 to see how it EVALUATES...
[tt]
VLOOKUP($AD$4, table,(AC11+1),FALSE)
[/tt]
$AD$4 is the first cell in your lookup range. WHY are you using that cell? It does not make sense.

What is the value...
AC11+1

Is table a named range (that's a very explicitly defined thing)? What range?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jhogie]

Thanks for the reply and the 'F9' tip. The purpose of AC11+1 is to look up the column to the right of the one corresponding to AC11.

I have figured out a solution that works:
=IF(X8="Remove From Program","Remove From Program",IF(X8>20,VLOOKUP($AD$4,$AD$3:$AI$7,AC8+1,FALSE),IF(X8>=17,VLOOKUP($AD$5,$AD$3:$AI$7,1+AC8,FALSE),IF(X8>=11,VLOOKUP($AD$6,$AD$3:$AI$7,1+AC8,FALSE),VLOOKUP($AD$7,$AD$3:$AI$7,1+AC8,FALSE)))))

I replaced 'Table' with the table range '$AD$3:$AI$7'. I still don't know why the original worked for some but not others.

 

[SkipVought]

You do not need nested IF statements, one for each row.

Use a formula like this...
[tt]
=VLOOKUP($A$1,$B$2:$G$4,COLUMN()-1,TRUE)
[/tt]
sort your table and CHANGE the values in the first col, like this...
[tt]
1 IS-4 IS-4 IS-4 IS-3 IS-2
17 IS-4 IS-4 IS-3 IS-2 IS-1
21 IS-4 IS-3 IS-2 IS-1 IS-1
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}