Returning multiple Max results

AmiExcel

New Member
Joined
Feb 11, 2022
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
Hi wondering if anyone could help?

I have a report on questionnaires I'm doing where I have to return the MAX and MIN values of the results. I first started using the =VLOOKUP(MAX(C4:C12),C4:D12,2,0) formula but it only returns the first MAX value to me but instead I want a formula that can return all the MAX values to me.

To illustrate:

Satisfaction with…
Mental Health
2​
Physical Health
3​
Job Situation
5​
Accommodation
4​
Leisure Activities
1​
Friendships
5​

If I use the aforementioned formula it will tell me that the highest score would be 'Job situation' (as it's the first one with the MAX value) however when I look at the data 'Friendship' has the same score. Is there a formula that can return all the MAX values (e.g. tell me both 'Job Situation' and 'Friendships' are the highest scoring answers)? If there is would it also work for MIN values?

Thank you! I hope I have explained this well and let me know if there are any further questions.

Also, I'm using Excel 2019 if that helps.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hi & welcome to MrExcel.
How about
+Fluff 1.xlsm
ABCD
1Satisfaction with…
2Mental Health2Job Situation
3Physical Health3Friendships
4Job Situation5 
5Accommodation4
6Leisure Activities1
7Friendships5
Data
Cell Formulas
RangeFormula
D2:D4D2=IFERROR(INDEX($A$2:A7,AGGREGATE(15,6,(ROW($A$2:$A$7)-ROW($A$2)+1)/($B$2:$B$7=MAX($B$2:$B$7)),ROWS(D$2:D2))),"")
 
Upvote 0
Hi & welcome to MrExcel.
How about
+Fluff 1.xlsm
ABCD
1Satisfaction with…
2Mental Health2Job Situation
3Physical Health3Friendships
4Job Situation5 
5Accommodation4
6Leisure Activities1
7Friendships5
Data
Cell Formulas
RangeFormula
D2:D4D2=IFERROR(INDEX($A$2:A7,AGGREGATE(15,6,(ROW($A$2:$A$7)-ROW($A$2)+1)/($B$2:$B$7=MAX($B$2:$B$7)),ROWS(D$2:D2))),"")
Hi

Thank you! You are a godsend. It works perfectly!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,226,735
Messages
6,192,733
Members
453,752
Latest member
Austin2222

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top