I have a table in sheet 1 which consists of customer referral dates, names, and referral statuses:

I then have another table in sheet 2 showing the 5 oldest customer referrals which are still open:

The date column works fine. However, I'm running into problems when one or more customers have the same referral date. When matching the customer's name with their referral date, Excel always goes for the first result. As you can see in the oldest referrals table, customer "B" appears twice when it should actually be "B" & "C".

Here's the formulas I'm using:

To grab the dates: =SMALL(IF(Sheet1!C:C="Open", Sheet1!A:A),1)

To match the dates with a name: =INDEX(Sheet1!B:B,MATCH(1,(Sheet2!B3=Sheet1!A:A)*(Sheet1!C:C="Open"),0))

Could anybody suggest a solution please?

CodePudding user response：

If the data on Sheet1 are always sorted in date order then the formula below will yield the correct name:

=INDEX(Sheet1!$B$2:$B$8,MATCH(Sheet2!B3&"Open",Sheet1!$A$2:$A$8&Sheet1!$C$2:$C$8,0)-1 COUNTIF($B$3:B3,B3))

(this assumes that the dates are in column B of Sheet2)

the formula will have to be entered as an array formula if you don't have dynamic arrays