Problems with If Then Statements

[kenbonnell]

I am trying to use Excel to construct an application that would contain our 9 different locations and when the correct location is selected the address automatically drops in. I have the names of all our locations in a list and when they are selected, it goes to another cell and "pulls forward" the address. It works perfectly until I add our lab in Vacaville, does anyone know why? Does it have anything to do with limits set on 7 nested "If"s? Does anyone know of a different way to do this?

Here is the current formula:

=IF('Input Form'!B28="Chicago",'Input Form'!AR62,IF('Input Form'!B28="Franklin",'Input Form'!AS62,IF('Input Form'!B28="Gibbstown",'Input Form'!AT62,IF('Input Form'!B28="Research Triangle Park",'Input Form'!AU62,IF('Input Form'!B28="San Diego",'Input Form'!AV62,IF('Input Form'!B28="Seattle",'Input Form'!AW62,IF('Input Form'!B28="St. Louis",'Input Form'!AX62,IF('Input Form'!B28="Sugarland",'Input Form'!AY62,IF('Input Form'!B28="Vacaville",'Input Form'!AZ62,"")))))))))

Any help at all would be greatly appreciated!

 

[erik.van.geit]

Hi, kenbonnell
Welcome to the Board !!!!!

seems like you would need VLOOKUP
take a look at that function

kind regards,
Erik

 

[Nimrod]

IF does have a limitation of 7 ... however Vlookup does not ...

=INDIRECT((VLOOKUP('Input Form'!B28,{"Chicago","'Input Form'!AR62";"Franklin","'Input Form'!AS62";"Gibbstown","'Input Form'!AT62"},2,FALSE)))