Excel 2016 - INDEX MATCH

[Kpowz]

Hi there,

I am looking at returning the highest and lowest values from some data based on the JOB ID column and also return the gender of this person in separate column.

So I need it to look at the data return the highest and lowest values from the incumbent data and also the gender of the individual with the highest and lowest data.

I have tried doing a vlookup and nested if function but can't seem to get this to work.

Please see attached workbook with the desired columns highlighted in yellow on the 'master' sheet.

Many thanks,

[Fluff13]

Hi
In D2
=LARGE(IF(JOBEVAL!$G$3:$G$42=C3,JOBEVAL!$F$3:$F$42,""),1)
In F2
=SMALL(IF(JOBEVAL!$G$3:$G$42=C3,JOBEVAL!$F$3:$F$42,""),1)

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Then normal formulae in E2 & G2
=INDEX(JOBEVAL!$D$3:$D$42,MATCH(C3&"|"&D3,INDEX(JOBEVAL!$G$3:$G$42&"|"&JOBEVAL!$F$3:$F$42,0),0))
=INDEX(JOBEVAL!$D$3:$D$42,MATCH(C3&"|"&F3,INDEX(JOBEVAL!$G$3:$G$42&"|"&JOBEVAL!$F$3:$F$42,0),0))

[PaulM100]

Formula in:
D2: =SUMPRODUCT(MAX((JOBEVAL!$H$3:$H$42=$B3)*JOBEVAL!$F$3:$F$42))
E2: =LOOKUP(2,1/(JOBEVAL!$H$3:$H$42=$B3)/(JOBEVAL!$F$3:$F$42=$D3),JOBEVAL!$D$3:$D$42)
F2: =MIN(IF(JOBEVAL!$H$3:$H$42=$B3,JOBEVAL!$F$3:$F$42)) Array entered(Ctrl+Shift+Enter instead of regular enter)
G2: =LOOKUP(2,1/(JOBEVAL!$H$3:$H$42=$B3)/(JOBEVAL!$F$3:$F$42=$F3),JOBEVAL!$D$3:$D$42)