Forum Discussion
Excel: Finding Highest & Lowest Salaries by job title
Hi there
I am trying to find a formula that will help complete the summary page in the example attached.
I need to return the highest and lowest salaries by Job title. I tried VlookupMax and Min and they didn't work. Any suggestions?
I want to the formula to look at all the senior engineers salaries and find and return the lowest values.
Any help would be much appreciated!
6 Replies
As variant
=SMALL( UNIQUE( FILTER(Data!B:B, Data!A:A = A2) ), 1)and
=LARGE( UNIQUE( FILTER(Data!B:B, Data!A:A = A2) ), 1)EDV2021 Done! Solution solved! this question requires logical reasoning functions like MAXIFS AND MINIFS to be used not Pivot table! To confirm the result is right! i use filter function or advanced filter to check the max salary for each criteria and it was True
You can download the solution file attached! With love from Nigeria!
Donald_Genes I read the comment in your file "Bonus! You can't use pivot table for this problem". I wonder what made you write that. The only sensible way to solve this is WITH a pivot table as suggested by Detlef_Lewin and use the Row label filter (or a slicer) to limit the output to the job title(s) desired.
See attached.
The question require formula not Pivot table! Not as if Pivot table can't be use! Infact i can use many other ways including Power query but sticking to the question which asked for formula to be used is the right Way
- Best way is a pivot table.
