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

 

[Aladin Akyurek]

How about having either...

1]

=IF(ISNA(MATCH($D2,INDEX('Lookup Table'!B:O,0,VLOOKUP($B2,AreaTable,2,1)),0)),VLOOKUP($B2,AreaTable,3,1),INDEX('Lookup Table'!B:O,MATCH($D2,INDEX('Lookup Table'!B:O,0,VLOOKUP($B2,AreaTable,2,1)),0),VLOOKUP($B2,AreaTable,2,1)+1))

or, if you have the morefunc add-in...

2]

=IF(ISNA(SETV(MATCH($D2,INDEX('Lookup Table'!B:O,0,VLOOKUP($B2,AreaTable,2,1)),0))),VLOOKUP($B2,AreaTable,3,1),INDEX('Lookup Table'!B:O,GETV(),VLOOKUP($B2,AreaTable,2,1)+1))

which exploits the lay-out of your data on sheet Lookup Table?

AreaTable is a range on a separate sheet called Admin...

Book10
	A	B	C	D
1	Area Table
2	Area	Col in LT	Type
3	Florida	1	CONTRACTOR
4	Georgia	4	INHOUSE
5	Illinois-Indiana	7	INHOUSE
6	MidAtlantic/West	11	INHOUSE
7	Missouri-Kentucky	13	INHOUSE
8
Admin

AreaTable associates geographic areas to column numbers on Lookup Table. BTW, why not shorten the name Lookup Table to just LT?