Arrays with multiple search criteria

[Simon629]

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.

[TMS]

Suggest you post a sample workbook.

Regards, TMS

[Simon629]

Thanks TMS - have created a simple one that isn't 40MB in size, and just has the templates necessary.

Sample.xlsx

[Simon629]

I've worked it out - I need to nest the IF statements:

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

Seems so obvious in hindsight...

Still no idea how to return it in a different order though...