Index/Match Large with Criteria

[Celticfc]

Hi Mr Excel,

I've literally spent 3+ hours on Google but still can't solve the query below.


The code below works perfect, it gives me all the employee names ranked by salary.

Current Code:

=INDEX(Table1[Employee],MATCH(1,INDEX((Table1[Salary]=LARGE(Table1[Salary],ROWS(K$1:K1)))*(COUNTIF(K$1:K1,Table1[Employee])=0),),0))

I just can't figure out where to put these 2 criteria within the code above which will allow me to list my active female employees only:

if(Table1[Gender]="F")*(table1[Status]="Active")

 

[Aladin Akyurek]

Dou you also want to list the salaries of the active female employees along with their names or not?

 

[Celticfc]

Dou you also want to list the salaries of the active female employees along with their names or not?

Hi Aladin,

Yes absolutely,

I still couldn't find a solution so your support is much appreciated, thanks.

 

[Aladin Akyurek]

Hi Aladin,

Yes absolutely,

I still couldn't find a solution so your support is much appreciated, thanks.

Book1
	A	B	C	D	K	L
1	Employee	Salary	Gender	Status	3
2	linda	40000	F	Inactive	employee	salary
3	jon	31990	M	Active	scarlet	60000
4	karen	25000	F	Inactive	henriette	25000
5	henriette	25000	F	Active	wing yu	25000
6	dave	38905	M	Active
7	nate	29626	M	Inactive
8	wing yu	25000	F	Active
9	brian	43381	M	Inactive
10	scarlet	60000	F	Active
11	mete	60000	M	Active
Sheet1

In K1 just enter:

=COUNTIFS(Table1[Gender],"F",Table1[Status],"Active")

In K3 control+shift+enter, not just enter, and copy down:

=IF($L3="","",INDEX(Table1[Employee],SMALL(IF(Table1[Gender]="F",IF(Table1[Status]="Active",IF(Table1[Salary]=$L3,ROW(Table1[Employee])-ROW(INDEX(Table1[Employee],1,1))+1))),COUNTIFS($L$3:L3,L3))))

In L3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($L$2:L2)>$K$1,"",LARGE(IF(Table1[Gender]="F",IF(Table1[Status]="Active",Table1[Salary])),ROWS($L$2:L2)))