Lookup multiple values

[Mdsocal10]

I trying to write a formula to lookup automotive manufacturers and return 1, 2 or 3 depending on the specific manufacturer.

column W = Honda, Toyota, GMC, Chevrolet, BMW. Audi etc.

I wanting to lookup these values and return 1 for Asian, 2 for Domestic and 3 for European

Please help

 

[Fluff]

Hi & welcome to MrExcel.
Do you have a list of the manufacturers & what region they are? If so you could use VLOOKUP.

 

[Mdsocal10]

Hi & welcome to MrExcel.
Do you have a list of the manufacturers & what region they are? If so you could use VLOOKUP.

It's easier than that, column has multiple manufacturers and i want to create a grouping 1 for Asian vehicles 2 for domestic vehicles and 3 for European vehicles

 

[Mdsocal10]

simple formula would be =IF(W2="Toyota",1)

 

[Mdsocal10]

i want to expand that to include the multiple manufacturers

If(W2=Toyota, Acura etc return 1 or if(W2=GMC, Chevrolet etc retrun 2 or If(W2=BMW, Mercedes-Benz etc retrun 3

 

[Fluff]

You would be far better off creating a lookup list of the manufacturers & their region code, rather than a long nested if/or formula

 

[Mdsocal10]

=IF(OR(W3="Hyundai", W3="Toyota",W3="Honda", W3="Acura",W3= "Lexus", W3="Subaru", W3="Mitsubishi", W3="Nissan", W3="Mazda", W3="Infiniti", W3="Kia",W3="Suzuki",W3="Scion", W3="Isuzu", W3="Genesis"),"1","")

This is what I have that works however I need to expand that to include Domestic and European makes. Domestic returning 2 and European returning 3

 

[Fluff]

Which is the precise reason I am suggesting you change that & have a table with that data. It is simpler, cleaner & far far easier to maintain.
Like

+Fluff 1.xlsm
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2	Hyundai	1															Lexus	1
3	Toyota	1															Kia	1
4	Honda	1															VW	2
5	Acura	1
6	Lexus	1
7	Subaru	1
8	Mitsubishi	1
9	Nissan	1
10	Mazda	1
11	Infiniti	1
12	Kia	1
13	Suzuki	1
14	Scion	1
15	Isuzu	1
16	Genesis	1
17	BMW	2
18	Audi	2
19	VW	2
20	GMC	3
21
Main

Cell Formulas
Range	Formula
X2:X4	X2	=VLOOKUP(W2,$G$2:$H$20,2,0)

 

[Mdsocal10]

Ah, the light just went off. Thanks for getting me out of the weeds! Happy New Year

 

[Fluff]

You're welcome & thanks for the feedback.