INDEX array formula to return multiple values using multiple criteria

[sifar786]

Hi,


I have a table like this:

A B C D

Country1 Country2 Country3

1 Service1 cat horse ass

2 Service1 dog leopard mule

3 Service2 mice jaguar zebra

4 Service2 rat buffalo wilderbeast

5 Service2 toad ox lion

i want to populate a data validation list using a named range created by filtering values in above table on below 2 criteria:


Services : Service2

Country : Country3


I am trying something like this which is not working properly:


=IF(ISERROR(INDEX($F$2:$H$6,--IF($E$2:$E$6=$A$2,$E$2:$E$6),$B$1)),"",INDEX($F$2:$H$6,--IF($E$2:$E$6=$A$2,$E$2:$E$6),$B$1))

where $B$1 = column no in range $E$2:$E$6 .


the problem is:

i am getting the reverse values in the column.

e.g. for Service1 & Country3 criteria,

i should be getting:

ass

mule


but i am getting reverse:

zebra

wilderbeast

lion


can somebody find a way to reverse the formula row numbers? if you want i can post the link to this table workbook.

 

[sifar786]

hi,


changed formula this way:


{=IF(ISERROR(INDEX($F$2:$H$6,SMALL(IF($A$2=$E$2:$E$6,ROW($E$2:$E$6)-ROW($E$2)+1),ROW($E$2:$E$6)-ROW($E$2)+1),$B$1)),"",INDEX($F$2:$H$6,SMALL(IF($A$2=$E$2:$E$6,ROW($E$2:$E$6)-ROW($E$2)+1),ROW($E$2:$E$6)-ROW($E$2)+1),$B$1))}