Help with Vlookup in array of data

[zillafreak]

Hello, I am having trouble with vlookup formula. I am trying to search in an array of data for the max/min numbers and have it return name of a person associated with the number.
My data is as follows.

	Week1	Week2	Week3	Week 4	Week5
Jason	50	55	60	69	5
Nick	20	45	10	40	20
Bob	30	40	50	15	55

I am trying to do a cell that spits out the person with the max score.
First I have a cell that gives the max score (=max(B2:F4)) which is 69. Then I would like the cell next to it to display the person with the score aka Jason. I cant figure out how to search in multiply columns and rows then give the numbers.

thanks for any help.

 

[Sufiyan97]

Try

Book2
	A	B	C	D	E	F	G
1		Week1	Week2	Week3	Week 4	Week5
2	Jason	50	55	60	69	5
3	Nick	20	45	10	40	20
4	Bob	30	40	50	15	55
5
6		50	55	60	69	55
7		Jason	Jason	Jason	Jason	Bob
8
9
Sheet1

Cell Formulas
Range	Formula
B6:F6	B6	=MAX(B2:B4)
B7:F7	B7	=INDEX($A$2:$A$4,MATCH(B6,B2:B4,0))

 

[Fluff]

What should happen if two people get the same max score?

 

[zillafreak]

Try

Book2
	A	B	C	D	E	F	G
1		Week1	Week2	Week3	Week 4	Week5
2	Jason	50	55	60	69	5
3	Nick	20	45	10	40	20
4	Bob	30	40	50	15	55
5
6		50	55	60	69	55
7		Jason	Jason	Jason	Jason	Bob
8
9
Sheet1

Cell Formulas
Range	Formula
B6:F6	B6	=MAX(B2:B4)
B7:F7	B7	=INDEX($A$2:$A$4,MATCH(B6,B2:B4,0))

This is for each week. I am trying to do for the overall.

 

[Sufiyan97]

Thanks Fluff

This might work

Book2
	A	B	C	D	E	F	G
1		Week1	Week2	Week3	Week 4	Week5
2	Jason	50	55	60	69	5
3	Nick	20	45	10	40	20
4	Bob	30	40	50	15	55
5
6	Max Score	50	55	60	69	55
7
8	Person 1	Jason	Jason	Jason	Jason	Bob
9	Person 2
10	Person 3
11	Person 4
12	Person 5
13	Person 6
14	Person 7
15
Sheet1

Cell Formulas
Range	Formula
B6:F6	B6	=MAX(B2:B4)
B8:F14	B8	=IFERROR(INDEX($A$2:$A$4,AGGREGATE(15,6,ROW($B$2:$B$4)-ROW($A$2)+1/(B$2:B$4=B$6),ROW($A1))),"")

 

[Fluff]

Can you please answer my question?

 

[zillafreak]

What should happen if two people get the same max score?

After 7 weeks, we had a couple close scores, but none were the same. it is possible, but I haven't seen this happen yet. Plus the chance of two teams having the same max and min is even lower.



This is my table of data. The #n/a cells, I am trying to get them to say the team name the corresponds to that score.
So for the max of 232.88, it should say Goose. Surprisingly, the min of 85.74 should also say Goose.

This is for my Fantasy Football league and I am trying to use it show some stats for everyone. Also doing this to help better my excel skills.

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1		Week1	Week2	Week3	Week 4	Week5
2	Jason	50	55	60	69	5		5	Jason
3	Nick	20	69	10	40	20		69	Jason, Nick
4	Bob	30	40	50	15	55
Main

Cell Formulas
Range	Formula
H2	H2	=MIN(B2:F4)
I2	I2	=TEXTJOIN(", ",,IF(B2:F4=H2,A2:A4,""))
H3	H3	=MAX(B2:F4)
I3	I3	=TEXTJOIN(", ",,IF(B2:F4=H3,A2:A4,""))

 

[zillafreak]

Ok, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1		Week1	Week2	Week3	Week 4	Week5
2	Jason	50	55	60	69	5		5	Jason
3	Nick	20	69	10	40	20		69	Jason, Nick
4	Bob	30	40	50	15	55
Main

Cell Formulas
Range	Formula
H2	H2	=MIN(B2:F4)
I2	I2	=TEXTJOIN(", ",,IF(B2:F4=H2,A2:A4,""))
H3	H3	=MAX(B2:F4)
I3	I3	=TEXTJOIN(", ",,IF(B2:F4=H3,A2:A4,""))

Thank you so much.
This worked perfectly.

 

[Fluff]

Glad we could help & thanks for the feedback.