Nested IF's and VLookup Tables

[darkangelstorm]

Hi if anyone could help me i would be grafeful, the problem im having is
trying to use nested if's with vlookup tables.

for example..

=IF(D20=VLOOKUP(D20,crap,1),VLOOKUP(D20,crap,2),VLOOKUP(D20,to,2))

so im saying if the cell D20 contains a description that fits in "crap"
table then go to collum "index 2" or goto a differnet table called "to"
in this case. as the for the false statemnet of "IF"

(The forumla above works)

however im trying to add another table to the chain, for example if it
does not table "crap or to" then please goto table "lol"


This was my attempt.

"=IF(D19=VLOOKUP(D19,crap,1),VLOOKUP(D19,crap,2),IF(D91=VLOOKUP(D19,to,1),VLOOKUP(D19,to,2),VLOOKUP(D19,,lol,2)))"

however im getting N/A's for table two and three now,

where as table 1 = crap
table 2 = to
table 3 = lol

anyhelp would be grateful. DAS

 

[Paul]

Hi if anyone could help me i would be grafeful, the problem im having is
trying to use nested if's with vlookup tables.

for example..

=IF(D20=VLOOKUP(D20,crap,1),VLOOKUP(D20,crap,2),VLOOKUP(D20,to,2))

so im saying if the cell D20 contains a description that fits in "crap"
table then go to collum "index 2" or goto a differnet table called "to"
in this case. as the for the false statemnet of "IF"

(The forumla above works)

however im trying to add another table to the chain, for example if it
does not table "crap or to" then please goto table "lol"


This was my attempt.

"=IF(D19=VLOOKUP(D19,crap,1),VLOOKUP(D19,crap,2),IF(D91=VLOOKUP(D19,to,1),VL
OOKUP(D19,to,2),VLOOKUP(D19,,lol,2)))"

however im getting N/A's for table two and three now,

where as table 1 = crap
table 2 = to
table 3 = lol

anyhelp would be grateful. DAS

You have D91 (rather than D19) immediately after the second IF. Also, you
have two adjacent commas (should just be one) in the last VLOOKUP.

 

[Mark Graesser]

VLOOKUP will return a #N/A error if the item you are looking for isn't in the table. A typical way around this is to use the ISNA() function. Instead of seeing if your item is in the lookup table, you check to see if it isn't in the lookup table. You will need to reverse the true and false statements in your IF statement

Good Luck
Mark Graesser

 

[darkangelstorm]

heh, using the ISNA() function will take me forever to type out the
forumla and i want to find a quicker way to do it ......

also about the double comma's.


" =IF(D19=VLOOKUP(D19,crap,1),VLOOKUP(D19,crap,2),IF
(D91=VLOOKUP(D19,to,1),VL
OOKUP(D19,to,2),VLOOKUP(D19,,lol,2)))"

if i take it out, excel complains, i don't know why, ah i see, about
the D91 factor though, thanx for pointing that out... lets hope it
works

 

[darkangelstorm]

no it doesnt work, and i don't want to use the ISNA () function.

is there any other alternative soloution other than using ISNA (

 

[Harlan Grove]

no it doesnt work, and i don't want to use the ISNA () function.

Fine. Lovely. Use ISERROR instead. Those are your only alternatives. If you
don't like 'em, too damn bad.

is there any other alternative soloution other than using ISNA ()

Actually, yes, but the resulting formulas would be evel longer.

 

[Harlan Grove]

...
...

This was my attempt.

"=IF(D19=VLOOKUP(D19,crap,1),VLOOKUP(D19,crap,2),
IF(D91=VLOOKUP(D19,to,1),VLOOKUP(D19,to,2),VLOOKUP(D19,,lol,2)))"

however im getting N/A's for table two and three now,

...

Alternatives not using ISNA or ISERROR (and fixing the D91 and the double comma
typos - the latter *IS* an error in the making because while it's syntactically
possible to pass VLOOKUP a missing 2nd argument, the result will necessarily be
an error).

=IF(COUNTIF(INDEX(crap,0,1),D19),VLOOKUP(D19,crap,2),
IF(COUNTIF(INDEX(to,0,1),D19),VLOOKUP(D19,crap,2),
VLOOKUP(D19,lol,2)))

 

[Harlan Grove]

...
...

=IF(COUNTIF(INDEX(crap,0,1),D19),VLOOKUP(D19,crap,2),
IF(COUNTIF(INDEX(to,0,1),D19),VLOOKUP(D19,crap,2),
VLOOKUP(D19,lol,2)))

Too much crap. Make that

=IF(COUNTIF(INDEX(crap,0,1),D19),VLOOKUP(D19,crap,2),
IF(COUNTIF(INDEX(to,0,1),D19),VLOOKUP(D19,to,2),
VLOOKUP(D19,lol,2)))

 

[darkangelstorm]

thanx for the forumula. although it half works i think i can sort i
out. thanx agai