Find Manufacturer from product name

[tonywatsonhelp]

Hi Everyone,
OK so I have A product name is cell J7,

then I have all my products listed in range N2:Z100
In column M I have the manufacture's names,
I want a formula that can look at the name in J7, find it in the range N2:Z100,
Find the name of that lines manufacturer and return the manufacturer's name.
Please help if you can
Thanks
Tony

 

[Glenn Kennedy]

Maybe this...

Book2
	J	K	L	M	N	O	P	Q	R	S
1
2				M1	QQQ	AAA	SSS
3				M2	WWW	DDD	FFF	GGG
4				M3	EEE	HH
5				M4	RRR	JJJ	KKK
6				M5	TTT	LLL	ZZZ	XXX	CCC
7	AAA	M1		M6	YYY	VVV
8
9
10
11
Sheet1

Cell Formulas
Range	Formula
K7	K7	=INDEX($M$2:$M$100,SUM(INDEX(($N$2:$W$100=$J7)*ROW($N$2:$W$100),))-ROW($N$2)+1)

 

[Glenn Kennedy]

W100 should read Z100... I forgot to change it!!

 

[tonywatsonhelp]

Thank you, works great