# Search array for all instances of a date, and criteria from separate column, return names from all matching rows

#### DogWithWiFi

##### New Member
Hey all, I can't figure out how to pull this off with a single formula, or what the best approach might be (FILTER, INDEX & MATCH, ?).

I want to return a list of Names (and the associated Status) from all rows where: Any of the dates in D:F match the dynamic date in I3 and the Status in C matches the dynamic value in I2.

In my example, the list should return:
Bob | Approve
Chris | Approve
Kelly | Approve

Any help is appreciated - thanks!

#### Attachments

• MatchDateStatus.png
27.6 KB · Views: 7

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### StephenCrump

##### MrExcel MVP
Welcome to the Forum!

Try: =FILTER(A2:A6,(C2:C6=I2)*((D2:D6=I3)+(E2:E6=I3)+(F2:F6=I3)),"None found")

The status is redundant (it will always match I2), but if you must show it: =FILTER(A2:A6&" | "& I2,(C2:C6=I2)*((D2:D6=I3)+(E2:E6=I3)+(F2:F6=I3)),"None found")

#### DogWithWiFi

##### New Member
Hey Stephen, thanks for the welcome and the response!

I should have mentioned in my op that I have a version of your example working, but the dates in my live dataset are spread across ~50 columns (as opposed to just 3), so I thought I'd created a needlessly long formula, and was unaware of a "better" and/or more concise option. (If you think there is a better/more concise formula solution in this scenario, please let me know).

Thanks again - I very much appreciate your expert input (and am glad to know I wasn't way off the mark...)!

#### StephenCrump

##### MrExcel MVP
=FILTER(A2:A6,(C2:C6=I2)*MMULT(--(D2:F6=I3),SEQUENCE(COLUMNS(D1:F1),,1,0)),"None found")

It's always better to give your full requirements up-front. For only three columns, I thought this would be overkill.

#### DogWithWiFi

##### New Member
Thanks again, Stephen (and apologies for any wasted time as a result of my oversight).

The most recent formula works perfectly on the sample data, but when I modified to correlate to my live data, something traced to the dates array (T:AN) is throwing up a persistent VALUE error. Haven't cracked it yet—all the date formatting looks to match—but I might be missing something obvious and will try again tomorrow...

=FILTER(Sheet1!\$E\$4:\$E\$5000,(Sheet1!\$AO\$4:\$AO\$5000=\$I\$2)*MMULT(--(Sheet1!\$T\$4:\$AN\$5000=\$I\$3),SEQUENCE(COLUMNS(Sheet1!T1:AN1),,1,0)),"None found")

Replies
0
Views
68
Replies
1
Views
222
Replies
1
Views
509
Replies
0
Views
90
Replies
4
Views
173

1,127,467
Messages
5,624,913
Members
416,064
Latest member
PaulBr2

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back