VLOOKUP formula help for high/low numbers of each day

redspanna

Well-known Member
Joined
Jul 27, 2005
Messages
1,462
Office Version
2016
Platform
Windows
Hi all

Book1
ABCD
1Sat 06-06-20Sun 07-06-20
2KEVIN1921
3PAUL1719
4PETER2118
5ALAN1813
6JEFF1920
7TONY1515
8SIMON1413
9
10HIGH
21
21
11PETERKEVIN
12LOW
14
13
13SIMONALAN
Sheet1
Cell Formulas
RangeFormula
C10:D10C10=MAX(C2:C8)
C12:D12C12=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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,639
Office Version
365
Platform
Windows
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

Active Member
Joined
Mar 22, 2020
Messages
473
Office Version
365, 2016
Platform
Windows
Hi, I had the same remark as @Peter_SSs
Suggestion to handle "ties" below.
Book1
ABCD
16/06/20207/06/2020
2KEVIN1921
3PAUL1719
4PETER2118
5ALAN1813
6JEFF1920
7TONY1515
8SIMON1413
9HIGH
PETER
KEVIN
10
 
 
11LOW
SIMON
ALAN
12
 
SIMON
Sheet1
Cell Formulas
RangeFormula
C9:D10C9=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:D12C11=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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,639
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Jul 27, 2005
Messages
1,462
Office Version
2016
Platform
Windows
@GraH
This will do the job just fine - thanks a lot !
 

redspanna

Well-known Member
Joined
Jul 27, 2005
Messages
1,462
Office Version
2016
Platform
Windows
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

Active Member
Joined
Mar 22, 2020
Messages
473
Office Version
365, 2016
Platform
Windows
@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. (y)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,639
Office Version
365
Platform
Windows
Thanks for updating your profile. (y)

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
ABCD
16/06/20207/06/2020
2KEVIN1921
3PAUL1719
4PETER2118
5ALAN1813
6JEFF1920
7TONY1515
8SIMON1413
9HIGH
PETER
KEVIN
10
 
 
11LOW
SIMON
ALAN
12
 
SIMON
redspanna (2)
Cell Formulas
RangeFormula
C9:D10C9=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:D12C11=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))),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,099,055
Messages
5,466,304
Members
406,474
Latest member
osama beskales

This Week's Hot Topics

Top