What else if not IF ?

[Azureo]

Hello everyone,


I have a sheet with ~ 1000 rows. Each row contains a name of a car store which a have to divide into 2 categories: Franchise or Independent. The franchise stores would be everything what contains words like e.g.: Acura, BMW, GM, Subaru, Audi, etc... and the Independent would be everything else. Is there any way in Ecxel to do it fast? I was thinking about IF function but there is more than 20 car brands to be included in the Franchise category.Thank you very much for your help guys. Below is the sample of the sheet:

Account Name Independent/Franchise

1000 Island Auto Group

1000 Islands Toyota

1000 Islands Used Vehicle Sales & Service

1st Auto Group

1st Rate Auto Sales

401 Dixie Hyundai

401 Dixie Infiniti

401 Dixie Mazda

401 Dixie Nissan

401 Dixie Volkswagen

4x4's N More

A.J. Dawson Automobiles Ltd

A2Z Auto Sales and Leasing Limited

AAA Auto Sales

AAA Auto Wholesale

Aaron Auto

Abbotsford Volkswagen

Acen Motors

ACES AUTO SALES

Achilles Mazda

Acura of Barrie

Acura of North Toronto

Acura on Brant

Acura Plus Blainville

Acura West

Addison Chevrolet Buick GMC

ADF Auto Rive-Sud

Adria Auto Centre

Advance Fine Auto

Agincourt Hyundai

 

[Faseeh]

Hi Azureo,


Welcome to the Forums!! What are the 20 Car-names??

 

[imgonna]

Hi


Assuming that the list of dealerships starts from A2.

Put list of Main Delerships (Mazada etc) into D2 downwards.


In E2 place: =ISNUMBER(LOOKUP(9.99999999999999E+307,FIND($D$2:$D$999,A2)))


This checks the string in A2 against the list of dealerships in D2:D999 (Make sure there are no blank cells in this range)


Returns True or False


Cheers

G

 

[Azureo]

I will try it. Thank you very much Glen

 

[Azureo]

Hi Faseeh,


The brands:

Ford

Toyota

Hyundai

Chevrolet

Honda

Dodge

Nissan

Ram

Mazda

Kia

GMC

Volkswagen

Jeep

Mercedes-Benz

Subaru

BMW

Mitsubishi

Audi

Chrysler

Acura

Buick

Lexus

Infiniti

Fiat

Suzuki

Cadillac

Lincoln

Scion

Volvo

Mini

Land Rover

Porsche

smart

Jaguar

Maserati

Bentley

Saab

Thanks

 

[Azureo]

Glen's formula returns only True value. Unfortunately I don't know how to fix it..

 

[Faseeh]

Hi Azureo,


Please wait for a moment... I am still working...

 

[Azureo]

Masters of Excel I hope my Excel skill will equal yours one day guys

 

[Faseeh]

Hi Azuero,


Please see this:


http://dl.dropbox.com/u/60644346/Vehicles_CountIf.xlsx


...am waiting for feedback


Regards,

 

[shrivallabha]

See if this works for you:


=IF(ISERROR(LOOKUP(999,FIND(Carlist,A2))),"Independent","Franchise")


where Carlist is a named range that lists all car brands.