Alphabetically Sort a dynamic range based on multiple filter values

[KJefferies]

Hi all

I am struggling to get an Array formula to sort based on Multiple search values.

I have 3 List.

when the excel was first written I only had List B and C and i used the following formula to create a result list of just the references in list C

{=IFERROR(INDEX(ListC,MATCH(ROW(1:1),IF(ListB="Manager",COUNTIFS(ListB,"Manager",ListC,"<" &ListC)+1,""),0)),"")} array formula

I am now trying to expend the result list to look at List A to get the filter values by changing the formula to

{=IFERROR(INDEX(ListC,MATCH(ROW(1:1),IF(ListB=ListA,COUNTIFS(ListB,ListA,ListC,"<" &ListC)+1,""),0)),"")}

But this does not get the results I am looking for. All 3 lists are dynamic named ranges so would like if possible to stick to the named ranges and due to the usage of the sheet I can not use VBA.

Can anyone help

Example List Data..

[TABLE="width: 67"]
<colgroup><col></colgroup><tbody>[TR]
[TD]ListA[/TD]
[/TR]
[TR]
[TD]Manager[/TD]
[/TR]
[TR]
[TD]Executive

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 67"]
<colgroup><col></colgroup><tbody>[TR]
[TD]ListB[/TD]
[/TR]
[TR]
[TD]Manager[/TD]
[/TR]
[TR]
[TD]Pre Sales[/TD]
[/TR]
[TR]
[TD]Sales Rep[/TD]
[/TR]
[TR]
[TD]Manager[/TD]
[/TR]
[TR]
[TD]Manager[/TD]
[/TR]
[TR]
[TD]Sales Rep[/TD]
[/TR]
[TR]
[TD]Sales Rep[/TD]
[/TR]
[TR]
[TD]Manager[/TD]
[/TR]
[TR]
[TD]Manager[/TD]
[/TR]
[TR]
[TD]Manager[/TD]
[/TR]
[TR]
[TD]Manager[/TD]
[/TR]
[TR]
[TD]Executive[/TD]
[/TR]
[TR]
[TD]Executive[/TD]
[/TR]
[TR]
[TD]Executive[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85"]
<colgroup><col></colgroup><tbody>[TR]
[TD]ListC[/TD]
[/TR]
[TR]
[TD]SF00001111[/TD]
[/TR]
[TR]
[TD]SS00001112[/TD]
[/TR]
[TR]
[TD]SPSB0512PS[/TD]
[/TR]
[TR]
[TD]JK00001001[/TD]
[/TR]
[TR]
[TD]PP00001113[/TD]
[/TR]
[TR]
[TD]BTSB0712TB[/TD]
[/TR]
[TR]
[TD]FFSB0713FF[/TD]
[/TR]
[TR]
[TD]RB00001114[/TD]
[/TR]
[TR]
[TD]PB00001115[/TD]
[/TR]
[TR]
[TD]EE00001116[/TD]
[/TR]
[TR]
[TD]BB00001117[/TD]
[/TR]
[TR]
[TD]NN00001118[/TD]
[/TR]
[TR]
[TD]BB00001119[/TD]
[/TR]
[TR]
[TD]AW00001120[/TD]
[/TR]
</tbody>[/TABLE]

 

[KJefferies]

I have been playing some more today whilst looking back at my notes when I wrote the xls in the first place. The Problem appears to be generated as part of the first range and criteria of the COUNTIFS statement. If you run this with a single value in LISTA the new formula works and the COUNTIFS(ListB,ListA part is returning the total count where the condition is met e.g. if you just have "Manager" in ListA this returns 7. However if you add to items to LISTA "MANAGER","EXECUTIVE" it does not return the total but returns an array with the total of each item {7,3}.

Any ideas how I can get the Total Returned.

I have also Noticed that the Condition of the IF statement fails if there are Multiple items in LISTA but I have been able to work round that by changing the "LISTB = LISTA" to be "ISNUMBER(MATCH(ListB,ListA,0))" which correctly creates the expected True and False list needed by the if statement.

Cheers fro any support folks can give.