Index match with multiple criteria - withn a date range!

[matthec2]

Hello,

Apologises if this this has been asked before - I have seen a few similar thread but nothing that specifically helps with my problem.

I am quite an advanced Excel user but my knowledge of array formulas is poor, something I would like to work on.

Problem:

I have a large dataset of customer bookings - with columns for customer postcode and the date they made a booking.

On a 2nd tab I have a list of special customer who attended events in the last 12 months. This tab has a column with a customer postcode and the date of the event (this would be the Minimum Date). I have added a column deadline date which is the event date +60 days (this would be the maximum column)

I already have a formula which tells me if any of the event customer appear on the master database within the date range (using a simple countif greater than zero)

I want to then use an Array Index Match to tell me which product they purchased (in another column)

Column Labels and Tabs:
Master_Bookings Tab

• Booking Date - Column F
• Customer Postcode - Column AH
• Cruise Line (this is the column with the product name I want the result to be) - Column H

Customer_Events Tab

• Event Date (Minimum Date) - Column D
• Deadline Date (Maximum Date) - Column G
• Customer Postcode - Column B

Many thanks, help appreciated!

 

[mart37]

=iserror(index(master!$h$1:$h$1000,sumproduct((b2=master!$ah$1:$ah$1000)*(d2<=master!$f$1:$f$1000)*(g2>=master!$f$1:$f$100)*rij($1:$1000))),"")

 

[Misca]

If there's only one row where the conditions match your could use SUMPRODUCT:

SUMPRODUCT((PostCodes=$B5)*(DateRange>=$D5)*(DateRange<G5)*ROW(DateRange)) returns the row number (or error).

Wrap it inside the INDIRECT formula and you should get the Cruise Line you're looking for:

=INDIRECT("Master_Bookings!D"&SUMPRODUCT((PostCodes=$B5)*(DateRange>=$D5)*(DateRange<G5)*ROW(DateRange)))

If there's more than one (or none) matching rows the formula will not work.