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.

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

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

..

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/

2. Multiple Nested IFs, ORs, Ands

3. Nesting IF's and AND's - a bit of help please...

5. Editing and Moviing from 6 If's to 7 Ifs (Nested)

6. nested ifs and end ifs and elses

7. Ifs and Ands - I'm confused!

8. IF's & AND's

9. Multiple ifs, and ands, and are not working

10. multiple ifs ands & indirect or offset?

11. two questions: and's inside if's and checking the return code of a program

12. multiple ifs & ands indirect/offset...need help desperately pl

13. IFs ANDs and ORs

14. multiple ifs & ands indirect/offset...need help desperately please

15. What is quicker? Nested or non nested ifs

7 post • Page:**1** of **1**