If then statements on excel

[trevor2524]

I have 3 option list in cell b2

What formula do I use so when an option is selected in b2 it automatically fills in cell c2,d2,e2,f2

an example is in b2 my options are

House, LLC
BTG, LLC
Blazing, LLC

When I select "House LLC" I want excel to automatically fill in the mailing address in cell

c2(Street address) d2(city) e2(state) f2(zipcode)

Can someone please help? I need to get this done quickly for my boss

[daffodil11]

Where is the address stored?

If you're able to provide a sample of your work, we'll be able to assist you much better.

[trevor2524]

They are all stored in dropdowns. So if a certain dropdown in selected in cell b2 then all the other dropdowns will change. If this doesn't help i will try and upload a copy of the my file. Thanks again for your help.

[daffodil11]

Assuming you have a table with company names and their respective data, you can just build series of 4 vlookups to pull it.

For example, somewhere you have R1:R10 Company Names, followed by their address, city, state, and zip in other columns.

If your dropdown for company is in B2, then in C2 you put: VLOOKUP(B2,$R$1:$V$10,COLUMN(B2),0)

Then just copy the formula out 4 columns.

[trevor2524]

i have the company names stored in Column CA. As well as the address city and state in the following columns. I inserted this formula into C2 like instructed =VLOOKUP(B2,$CA$1:$CA$3,COLUMN(B2),0) but it is not matching the company name from b2 with the companies listed into column CA. Can somebody please help me.

[daffodil11]

Here's how to use VLOOKUP()

VLOOKUP(look for what,where,pull from which column,0)

If your companies run from CA1 to CA5000, then you'll need to target CA1:CE5000 (including address, city, state, & zip)

For address, you'll be pulling column 2

For city, you'll be pulling column 3

State = column 4

Zip = column 5



C2 would look like =VLOOKUP(B2,$CA$1:$CE$5000,2,0)

D2 =VLOOKUP(B2,$CA$1:$CE$5000,3,0)

E2 =VLOOKUP(B2,$CA$1:$CE$5000,4,0)

F2 =VLOOKUP(B2,$CA$1:$CE$5000,5,0)

If you still aren't getting a match, then it just means the drop down list doesn't match the list of companies. If that's the case, I'd check both the list validation and the company names for trailing spaces, out of place commas and periods, or other similar issues.

[trevor2524]

Test.xlsx
I'm doing everything you guys are saying and I appreciate the help but for some reason it still won't work and I don't know what I'm over looking. I included a file in this reply so you can see exactly what I'm talking about. Again thanks for your help and the quick responses, I'm trying to get this done as soon as possible.

[daffodil11]

Worked fine for me. I put the formulas from my previous post in exactly the way I listed them.

C2 would look like =VLOOKUP(B2,$CA$1:$CE$5000,2,0)

D2 =VLOOKUP(B2,$CA$1:$CE$5000,3,0)

E2 =VLOOKUP(B2,$CA$1:$CE$5000,4,0)

F2 =VLOOKUP(B2,$CA$1:$CE$5000,5,0)


trevor.xlsx

[trevor2524]

Thank you it worked!