Help with multiple IF statement referencing text

[murraythek]

I have a drop down list created with 12 different vehicles both import and domestic.

I'm trying to create an if statement so that when only the imports are selected then the word "import" is populated in a specified cell. B1 is the drop down list. C1 is the specified cell for the word import.

Example: Example: =IF(B1=Fiat 500,"Import",IF(B1=Subaru Impreza,"Import",IF(B1=BMW 7 series,"Import")))

Excel is barking at me that the equals sign isnt correct after B1 and to add an ' but that doesnt work either. Please help.

 

[etaf]

=if ( OR ( B1="fiat 500", B1="Subaru", B1="BMW") , "Import", "")

so you need to create the list as shown - with the exaact wording it will find
or you could use a table and look the value up and choose import

depends on how many variations there are

 

[murraythek]

Thanks that works!

As a variation would it be possible to have multiple statements if I wanted to expand it further and break it down by decade? I realize its a bit more work as everything has to be typed out exactly but once done its done.

So now my list would look like:
2000's BMW
1990's BMW
1980's BMW
2000's Mercedes
1990's Mercedes
1980's Mercedes
1990's Volvo
1980's Volvo

Hope that makes sense! The goal is to show both Import and the associated decade.

 

[Tetra201]

You can try the following trick:

Add, say, three spaces at the end of each of your imports' names, then use the following formula:

=IF(RIGHT(B1,3)="   ","Import","")

 

[murraythek]

Doesn't seem to work.

What I need is statements for each decade (70's Import, 80's Import, 90's Import, 2000's Import and 2010's Import). Is this even possible with an IF statement?

 

[etaf]

you can use whatever text you like and match

 

[etaf]

=if ( OR ( B1="fiat 500", B1="Subaru", B1="BMW") , left(B1 , 6) & " Import", "")

you could search for BMW , Mercedes etc and then extract the first 6 characters

would that work ?

 

[murraythek]

Ok that sort of works but it only registers the first item selected in the drop down. I have double checked the formula and everything is in order. I assume the "6" in the "left(B1 , 6)" section counts over 6 spaces?

 

[etaf]

left(b1,6)

looks at the contens of cell B1
and extracts the first 6 characters from the left
ie
2000's
1800's

etc

if you put a sample on dropbox/onedrive to share the sample sheet

 

[murraythek]

I got it to work!!!! For whatever reason I had to redo my drop down list and now all the options are showing. Thanks everyone!!