Multiple IF Statements

[sstorm]

This is my formula:

=IF($B2="Missouri-Kentucky",VLOOKUP($D2,'Lookup Table'!$N$2:$O$1001,2,FALSE),IF(B2="Georgia",VLOOKUP(D2,'Lookup Table'!$E$2:$F$101,2,FALSE),IF(B2="MidAtlantic/West",VLOOKUP($D2,'Lookup Table'!$K$2:$L$31,2,FALSE),IF($B2="Illinois-Indiana",VLOOKUP($D2,'Lookup Table'!$H$2:$I$9,2,FALSE),IF($B2="Florida",VLOOKUP($D2,'Lookup Table'!$B$2:$C$134,2,FALSE))))))

This formula works for part of what I want. It is returning the values that I am looking for...if they are there. If they aren't there, it is returning NA. I usually use the IF(ISNA( function, but I can't figure out how to get it to work with this multiple IF statement. If the result is NA for Missouri-Kentucky, Georgia, MidAtlantic/West and Illinois-Indiana I want it to say "INHOUSE". If it is NA for Florida, I want it to say "CONTRACTOR". Can someone help???

Thanks!

Stacy

 

[Dutchy]

This should do it..

=IF(ISNA(VLOOKUP(D2,IF(B2="Missouri-Kentucky",'lookup table'!$N$2:$O$1001,IF(B2="Georgia",'lookup table'!$E$2:$F$1012,IF(B2="MidAtlantic/West",'lookup table'!$K$2:$L$31,IF(B2="Illinois-Indiana",'lookup table'!$H$2:$I$9,IF(B2="Florida",'lookup table'!$B$2:$C$134))))),2,FALSE)),IF(B2="Florida","CONTRACTOR", IF(B2="","",IN HOUSE)),VLOOKUP(D2,IF(B2="Missouri-Kentucky",'lookup table'!$N$2:$O$1001,IF(B2="Georgia",'lookup table'!$E$2:$F$1012,IF(B2="MidAtlantic/West",'lookup table'!$K$2:$L$31,IF(B2="Illinois-Indiana",'lookup table'!$H$2:$I$9,IF(B2="Florida",'lookup table'!$B$2:$C$134))))),2,FALSE))

Edit: altered formula to give blank if B2 is blank
Edit2: missed out the sheet name!

PS it would probably be easier to put your State references and possibly cell references in cells on the worksheet then reference them in the formula - that would make the formula shorter and more transparent and also allow you to make changes more easily

 

[sstorm]

=IF(ISNA(VLOOKUP($D2,IF(B2="Missouri-Kentucky",'Lookup Table'!$N$2:$O$1001,IF(B2="Georgia",'Lookup Table'!$E$2:$F$101,IF(B2="MidAtlantic/West",'Lookup Table'!$K$2:$L$31,IF($B2="Illinois-Indiana",'Lookup Table'!$H$2:$I$9,IF($B2="Florida",'Lookup Table'!$B$2:$C$134))))),2,FALSE)),if(b2="Florida","Contractor","Inhouse"),VLOOKUP(D2,IF(B2="Missouri-Kentucky",'Lookup Table'!$N$2:$O$1001,IF(b2="Georgia",'Lookup Table'!$E$2:$F$101,IF(B2="Illinois-Indiana",'Lookup Table'!$H$2:$I$9,IF(B2="Florida",'Lookup Table'!$B$2:$C$134))))),2,FALSE))

Am I missing something? It still doesn't work. It tells me that I have few too arguments...

 

[Dutchy]

SStorm

I don't think that's an exact copy of my final post - sorry I edited a couple of times.

Can you try copying again, it works for me!

 

[sstorm]

=IF(ISNA(VLOOKUP(D2,IF(B2="Missouri-Kentucky",'Lookup Table'!$N$2:$O$1001,IF(B2="Georgia",'Lookup Table'!$E$2:$F$1012,IF(B2="MidAtlantic/West",'Lookup Table'!$K$2:$L$31,IF(B2="Illinois-Indiana",'Lookup Table'!$H$2:$I$9,IF(B2="Florida",'Lookup Table'!$B$2:$C$134))))),2,FALSE)),IF(B2="Florida","CONTRACTOR", IF(B2="","",In house)),VLOOKUP(D2,IF(B2="Missouri-Kentucky",'Lookup Table'!$N$2:$O$1001,IF(B2="Georgia",'Lookup Table'!$E$2:$F$1012,IF(B2="MidAtlantic/West",'Lookup Table'!$K$2:$L$31,IF(B2="Illinois-Indiana",'Lookup Table'!$H$2:$I$9,IF(B2="Florida",'Lookup Table'!$B$2:$C$134))))),2,FALSE))

I copied it directly from your post and it gives me #NAME?

 

[Dutchy]

Sorry!

I think I've got it now. There needs to be quotes around IN HOUSE thus:

=IF(ISNA(VLOOKUP(D2,IF(B2="Missouri-Kentucky",'lookup table'!$N$2:$O$1001,IF(B2="Georgia",'lookup table'!$E$2:$F$1012,IF(B2="MidAtlantic/West",'lookup table'!$K$2:$L$31,IF(B2="Illinois-Indiana",'lookup table'!$H$2:$I$9,IF(B2="Florida",'lookup table'!$B$2:$C$134))))),2,FALSE)),IF(B2="Florida","CONTRACTOR", IF(B2="","","IN HOUSE")),VLOOKUP(D2,IF(B2="Missouri-Kentucky",'lookup table'!$N$2:$O$1001,IF(B2="Georgia",'lookup table'!$E$2:$F$1012,IF(B2="MidAtlantic/West",'lookup table'!$K$2:$L$31,IF(B2="Illinois-Indiana",'lookup table'!$H$2:$I$9,IF(B2="Florida",'lookup table'!$B$2:$C$134))))),2,FALSE))

 

[sstorm]

That seems to work! You're the best! Thanks so much!!

Stacy

 

[Brian from Maui]

I apologize for butting in but, maybe you should take a look at using Indirect along with the Vlookup. Naming your different ranges and whatever is in the B2 is dependant on the table to lookup. All those IF's can get confusing.

 

[Dutchy]

Brian,

Thanks, that's the sort of thing I suggested a few posts back!

giving it a go now....

 

[Brian from Maui]

Brian,

Thanks, that's the sort of thing I suggested a few posts back!

giving it a go now....

Instead of Indirect use Choose and Match. As an example, naming the ranges then using,

=VLOOKUP($D2,CHOOSE(MATCH(B2,{"A","B","C"},0),A,B,C),2,0))

Where A, B, and C are named ranges entered in B2

Edit,

If you don't have lots of this formula, I guess Indirect as in,

=VLOOKUP(D2,INDIRECT(B2),2,0)

Still naming the ranges