Hello, I am struggling and wonder if somebody can help me.

I have successfully created single-criteria arrays that returns a list of values. In their simplest form (i.e. removing IFERROR functions etc), the following arrays work. (I'll be honest that I don't fully understand why they work; I've just adapted examples I've found on the net).

=INDEX(Data,SMALL(IF(Type="Gold",ROW(Name)),ROW(1:1))-5,15) - this returns a list of names for all people that selected the gold service
=INDEX(Data,SMALL(IF(Date=$F$4,ROW(Name)),ROW(1:1))-5,15) - this returns a list of names for all people who booked on a certain date

Where the following are defined names for cells (in a different worksheet):
Data=all columns/rows of customer data (A:AR)
Date=column for date of booking (K:K)
Name=column for customer names (O:O)
Type=column for type of service (B:B)

What I need to do is search by both criteria (list of people who have a gold service on a certain date). My first thought was to use:

=INDEX(Data,SMALL(IF(AND(Type="Gold",Date=$F$4),ROW(Name)),ROW(1:1))-5,15

However, that returns a #VALUE error.

Any thoughts on how to restructure the formula to make it work?


For a bonus question, although this might not be possible, is there a way of returning the list in a different order to the source data, e.g. alphabetical order based on one of the columns (customer name) in the returned list?

Many thanks in advance.

Simon.