Index Match Help

[CoachJava]

I have ten columns. The first column (column A) is the sales reps' names. Columns B thru J are different codes referencing order # by clients. I need to look up the order # in columns B thru J and know the sales rep who completed the order. I know Index Match will work but am having problems with it for some reason. I have the value "777" in cell K2 and need the formula in cell L2. Any help would be appreciated. I cannot use a macro but need a formula.

[TABLE="class: grid, ******* 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Elec[/TD]
[TD]Carp[/TD]
[TD]Plum[/TD]
[TD]Exca[/TD]
[TD]Supp[/TD]
[TD]Ship[/TD]
[TD]Trav[/TD]
[TD]DryW[/TD]
[TD]Floo[/TD]
[/TR]
[TR]
[TD]Smith[/TD]
[TD]551[/TD]
[TD]842[/TD]
[TD]362[/TD]
[TD]422[/TD]
[TD]841[/TD]
[TD]368[/TD]
[TD]254[/TD]
[TD]128[/TD]
[TD]977[/TD]
[/TR]
[TR]
[TD]Jones[/TD]
[TD]440[/TD]
[TD]731[/TD]
[TD]251[/TD]
[TD]311[/TD]
[TD]730[/TD]
[TD]257[/TD]
[TD]143[/TD]
[TD]17[/TD]
[TD]866[/TD]
[/TR]
[TR]
[TD]Adams[/TD]
[TD]222[/TD]
[TD]333[/TD]
[TD]444[/TD]
[TD]555[/TD]
[TD]666[/TD]
[TD]777[/TD]
[TD]888[/TD]
[TD]999[/TD]
[TD]111[/TD]
[/TR]
[TR]
[TD]Ford[/TD]
[TD]789[/TD]
[TD]456[/TD]
[TD]123[/TD]
[TD]245[/TD]
[TD]567[/TD]
[TD]890[/TD]
[TD]678[/TD]
[TD]345[/TD]
[TD]901[/TD]
[/TR]
[TR]
[TD]Miller[/TD]
[TD]852[/TD]
[TD]741[/TD]
[TD]963[/TD]
[TD]107[/TD]
[TD]208[/TD]
[TD]309[/TD]
[TD]639[/TD]
[TD]528[/TD]
[TD]417[/TD]
[/TR]
[TR]
[TD]Williams[/TD]
[TD]573[/TD]
[TD]428[/TD]
[TD]689[/TD]
[TD]324[/TD]
[TD]125[/TD]
[TD]684[/TD]
[TD]268[/TD]
[TD]478[/TD]
[TD]660[/TD]
[/TR]
</tbody>[/TABLE]

 

[sheetspread]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Elec	Carp	Plum	Exca	Supp	Ship	Trav	DryW	Floo
2	Smith	551	842	362	422	841	368	254	128	977	777	Adams
3	Jones	440	731	251	311	730	257	143	17	866
4	Adams	222	333	444	555	666	777	888	999	111
5	Ford	789	456	123	245	567	890	678	345	901
6	Miller	852	741	963	107	208	309	639	528	417
7	Williams	573	428	689	324	125	684	268	478	660
Sheet3

Cell Formulas
Range	Formula
L2	=INDEX($A$1:$A$7,SUMPRODUCT(($B$2:$J$7=K2)*ROW($B$2:$J$7)))

 

[wideboydixon]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Elec	Carp	Plum	Exca	Supp	Ship	Trav	DryW	Floo
2	Smith	551	842	362	422	841	368	254	128	977	777	Adams
3	Jones	440	731	251	311	730	257	143	17	866
4	Adams	222	333	444	555	666	777	888	999	111
5	Ford	789	456	123	245	567	890	678	345	901
6	Miller	852	741	963	107	208	309	639	528	417
7	Williams	573	428	689	324	125	684	268	478	660
Sheet1

Cell Formulas
Range	Formula
L2	{=INDEX($A$1:$A$7,MIN(IF($A$1:$J$7=$K$2,ROW($A$1:$J$7))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD