Cross-Reference Function

[ninive]

I am trying to cross-reference the maximum value of column N (sales value for the month of March) with the correspondent name in column B (sales person). Basically I want the final result to be the name of the sales person who sold the most of that particular month.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I am trying to mix the MAX & VLOOKUP, but I don’t seem to be doing it right. Can someone help with this?<o></o>
Thank you<o></o>

 

[lenze]

Hi and Welcome to the Board!!!

Use INDEX/MATCH

=INDEX($B$2:$B$10,MATCH(MAX($N$2:$N$10),$N$2:$N$10,0),1)

What do you want if there's a tie for MAX?
lenze

 

[Jonmo1]

Try

=INDEX(B:B,MATCH(MAX(N:N),N:N,0))

Hope that helps...

 

[stanleydgromjr]

ninive,

Welcome to the MrExcel board.

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	A												100		D
3	B												200
4	C												300
5	D												1000
6	E												1
7	F												2
8	G												3
9	H												4
10	I												5
Sheet1

Have a great day,
Stan

 

[ninive]

Thank you so much Lenze. The formula works beautifully. Now I never thought about in the case of tie because it never happened, but I guess iI would like to split with two names.
Do you have a formula for that?
Once again thank you for your help!