Developer Forum

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 " *** 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


------------------------------------------------
~~ Message posted from http://www.yqcomputer.com/
~~View and post usenet messages directly from http://www.yqcomputer.com/

"darkangelstorm" < XXXX@XXXXX.COM > wrote in

"=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)))"

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


------------------------------------------------
~~ Message posted from http://www.yqcomputer.com/
~~View and post usenet messages directly from http://www.yqcomputer.com/

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

is there any other alternative soloution other than using ISNA (

-----------------------------------------------
~~ Message posted from http://www.yqcomputer.com/
~~View and post usenet messages directly from http://www.yqcomputer.com/

"Harlan Grove" wrote...
..

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

-----------------------------------------------
~~ Message posted from http://www.yqcomputer.com/
~~View and post usenet messages directly from http://www.yqcomputer.com/