If statement that looks at a range of cells

**harleychic67** · 09-06-2012, 04:06 PM

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!

**NBVC** · 09-06-2012, 04:13 PM

Try:

=VLOOKUP(H8,'DC Address List-Main'!$A:$I,2,FALSE)

change the 2 to get next columns...

**CheshireCat** · 09-06-2012, 04:16 PM

Try this:

=IF(H8="","",VLOOKUP(H8,'DC Address List-Main'!A:B,2,0))

**TMS** · 09-06-2012, 04:21 PM

I converted all the store numbers to numbers rather than text that looks like numbers ... in the address list and the DV List.

To be honest, you'd be better making the store number list on the address sheet a dynamic named range and using that in your DV.

Anyway, the lookup would then be:

=VLOOKUP(H8,'DC Address List-Main'!A:I,2,FALSE)

Regards, TMS

**harleychic67** · 09-06-2012, 04:40 PM

All of you ROCK! It was making me crazy and I knew I was making harder than it actually was; it works like a charm now! This site is definitely on my list of favorites! A million thank you's!