Nested IF's and VLookup Tables

  • Thread starter Thread starter darkangelstorm
  • Start date Start date
D

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
 
darkangelstorm said:
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
Click to expand...

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.
 
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
 
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
 
no :( it doesnt work, and i don't want to use the ISNA () function.

is there any other alternative soloution other than using ISNA (
 
no :( it doesnt work, and i don't want to use the ISNA () function.
Click to expand...

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 ()
Click to expand...

Actually, yes, but the resulting formulas would be evel longer.
 
...
...
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,
Click to expand...
...

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)))
 
...
...
=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)))
Click to expand...

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)))
 
thanx for the forumula. although it half works :) i think i can sort i
out. thanx agai
 
Back
Top