Nested IF's and VLookup Tables

Nested IF's and VLookup Tables

Post by darkangels » Tue, 18 Nov 2003 22:07:01



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/
 
 
 

Nested IF's and VLookup Tables

Post by Paul » Tue, 18 Nov 2003 22:29:29

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

 
 
 

Nested IF's and VLookup Tables

Post by TWFyayBHcm » Tue, 18 Nov 2003 22:41:05

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
 
 
 

Nested IF's and VLookup Tables

Post by darkangels » Wed, 19 Nov 2003 03:30:35


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/
 
 
 

Nested IF's and VLookup Tables

Post by darkangels » Thu, 20 Nov 2003 00:52:37


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/
 
 
 

Nested IF's and VLookup Tables

Post by Harlan Gro » Thu, 20 Nov 2003 03:15:41

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

Nested IF's and VLookup Tables

Post by darkangels » Sat, 22 Nov 2003 06:38:11


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/