Returning multiple Max results

[AmiExcel]

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.

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D
1	Satisfaction with…
2	Mental Health	2		Job Situation
3	Physical Health	3		Friendships
4	Job Situation	5
5	Accommodation	4
6	Leisure Activities	1
7	Friendships	5
Data

Cell Formulas
Range	Formula
D2:D4	D2	=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))),"")

 

[AmiExcel]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D
1	Satisfaction with…
2	Mental Health	2		Job Situation
3	Physical Health	3		Friendships
4	Job Situation	5
5	Accommodation	4
6	Leisure Activities	1
7	Friendships	5
Data

Cell Formulas
Range	Formula
D2:D4	D2	=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!

 

[Fluff]

You're welcome & thanks for the feedback.