Multiple IF Statements

sstorm

Board Regular
Joined
Nov 5, 2003
Messages
76
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Dutchy

Well-known Member
Joined
Sep 18, 2004
Messages
560
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

Board Regular
Joined
Nov 5, 2003
Messages
76
=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

Well-known Member
Joined
Sep 18, 2004
Messages
560
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

Board Regular
Joined
Nov 5, 2003
Messages
76

ADVERTISEMENT

=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

Well-known Member
Joined
Sep 18, 2004
Messages
560
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

Board Regular
Joined
Nov 5, 2003
Messages
76

ADVERTISEMENT

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

Stacy
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
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

Well-known Member
Joined
Sep 18, 2004
Messages
560
Brian,

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

giving it a go now....
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
Dutchy said:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,093
Members
414,501
Latest member
mdhaumyu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top