If statement that looks at a range of cells

[harleychic67]

I'm trying to populate a field based on a store # that I have made into a drop down box. Field H8 on the "Master BOL" tab is a drop down to pick the store #. After it is populated it copies the store # into AA1 (the only reason I did this was that my original IF stmt would not recgonize the # in the actual drop down field).

I want to write an IF statement that will compare AA1 to DC Address List-Main'!A2-A43 and based on the row that matches populate C8 on the Master BOL with the corresponding store name. So =IF(AA1='DC Address List-Main'!A2,'DC Address List-Main'!B2,IF(AA1='DC Address List-Main'!A3,'DC Address List-Main'!B3, etc... If it is 6006 I want C8 to show WAL-MART DC 6006D-DSDC and so on. I know that I can only nest 7 IF statements so I'm stumped. I also want to be able to populate the address and city/state/zip fields the same way. Thanks in advance for any and all help!