VLOOKUP formula help for high/low numbers of each day

[redspanna]

Hi all

Book1
	A	B	C	D
1			Sat 06-06-20	Sun 07-06-20
2	KEVIN		19	21
3	PAUL		17	19
4	PETER		21	18
5	ALAN		18	13
6	JEFF		19	20
7	TONY		15	15
8	SIMON		14	13
9
10		HIGH	21	21
11			PETER	KEVIN
12		LOW	14	13
13			SIMON	ALAN
Sheet1

Cell Formulas
Range	Formula
C10:D10	C10	=MAX(C2:C8)
C12:D12	C12	=MIN(C2:C8)

Can anybody suggest a formula that will look down the respective column for each day and provide the name of the person stored in column A with the highest number for that day and place into row 11 of that particular day. Then do same for the lowest and place in to row 13.

So for example, in my table above, for 6-6-20, the highest number is 21 and this was Peter, the lowest was 14 so this was Simon...

any help appreciated

TIA

 

[Peter_SSs]

Can you post how you want the results presented if multiple people have equal highest/lowest for a date?
Example: Can you post data and results again changing C2 to 21 and C6 & C7 to 14? (Or advise if this is not possible with your data.)

 

[GraH]

Hi, I had the same remark as @Peter_SSs
Suggestion to handle "ties" below.

Book1
	A	B	C	D
1			6/06/2020	7/06/2020
2	KEVIN		19	21
3	PAUL		17	19
4	PETER		21	18
5	ALAN		18	13
6	JEFF		19	20
7	TONY		15	15
8	SIMON		14	13
9		HIGH	PETER	KEVIN
10
11		LOW	SIMON	ALAN
12				SIMON
Sheet1

Cell Formulas
Range	Formula
C9:D10	C9	=IF(COUNTIF(C$2:C$8,MAX(C$2:C$8))<ROW(C1),"",INDEX($A$2:$A$8,AGGREGATE(15,6,(ROW(C$2:C$8)-1)/(C$2:C$8=MAX(C$2:C$8)),ROW(C1))))
C11:D12	C11	=IF(COUNTIF(C$2:C$8,MIN(C$2:C$8))<ROW(C1),"",INDEX($A$2:$A$8,AGGREGATE(15,6,(ROW(C$2:C$8)-1)/(C$2:C$8=MIN(C$2:C$8)),ROW(C1))))

 

[Peter_SSs]

I have another question that I forgot to ask: What version of Excel?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[redspanna]

@GraH
This will do the job just fine - thanks a lot !

 

[redspanna]

I have another question that I forgot to ask: What version of Excel?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Appreciate your help and good idea to update my account details - thanks

 

[GraH]

@GraH
This will do the job just fine - thanks a lot !

You're welcome and thanks for the feedback. Also good to see you followed Peter's advice to update your account.

 

[Peter_SSs]

Thanks for updating your profile.

I would suggest alternative formulas for two reasons:

1. These are shorter, with less function calls.
2. The post #3 formulas will return incorrect results if subsequently any rows are added at the top of the worksheet. These will not.

20 06 07.xlsm
	A	B	C	D
1			6/06/2020	7/06/2020
2	KEVIN		19	21
3	PAUL		17	19
4	PETER		21	18
5	ALAN		18	13
6	JEFF		19	20
7	TONY		15	15
8	SIMON		14	13
9		HIGH	PETER	KEVIN
10
11		LOW	SIMON	ALAN
12				SIMON
redspanna (2)

Cell Formulas
Range	Formula
C9:D10	C9	=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW(C$2:C$8)/(C$2:C$8=MAX(C$2:C$8)),ROWS(C$9:C9))),"")
C11:D12	C11	=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW(C$2:C$8)/(C$2:C$8=MIN(C$2:C$8)),ROWS(C$11:C11))),"")