KJefferies
New Member
- Joined
- Oct 20, 2017
- Messages
- 4
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..
<colgroup><col></colgroup><tbody>
</tbody>
<colgroup><col></colgroup><tbody>
</tbody>
<colgroup><col></colgroup><tbody>
</tbody>
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..
ListA |
Manager |
Executive |
<colgroup><col></colgroup><tbody>
</tbody>
ListB |
Manager |
Pre Sales |
Sales Rep |
Manager |
Manager |
Sales Rep |
Sales Rep |
Manager |
Manager |
Manager |
Manager |
Executive |
Executive |
Executive |
<colgroup><col></colgroup><tbody>
</tbody>
ListC |
SF00001111 |
SS00001112 |
SPSB0512PS |
JK00001001 |
PP00001113 |
BTSB0712TB |
FFSB0713FF |
RB00001114 |
PB00001115 |
EE00001116 |
BB00001117 |
NN00001118 |
BB00001119 |
AW00001120 |
<colgroup><col></colgroup><tbody>
</tbody>