Index match with multiple criteria (one with a wildcard) and multiple results.

[rachelsteele]

Hi all!

Any help with this would be greatly appreciated, as it's been a day now where I still haven't quite figured this out.

In my reference sheet, I have a column A for names, column B for timestamps, column C for ticket #, and so on.

In another sheet, I want to search for a specific name in column A and pull the adjacent timestamp from column B ONLY if the timestamp matches my wildcard criteria.

Reference sheet:

Name	Timestamp
Alex Smith	Mar 18, 2019, 7 AM
Alex Smith	Mar 18, 2019, 7 AM
Alex Smith	Mar 18, 2019, 8 AM
Alex Smith	Mar 18, 2019, 9 AM
Alex Smith	Mar 18, 2019, 12 PM
Alex Smith	Mar 18, 2019, 12 PM
Alex Smith	Mar 19, 2019, 7 AM
Alex Smith	Mar 19, 2019, 7 AM
Alex Smith	Mar 19, 2019, 7 AM
Alex Smith	Mar 19, 2019, 9 AM
Alex Smith	Mar 19, 2019, 9 AM
Alex Smith	Mar 19, 2019, 1 PM
Jennifer Lynch	Mar 18, 2019, 7 AM
Jennifer Lynch	Mar 18, 2019, 8 AM
Jennifer Lynch	Mar 19, 2019, 7 AM
Jennifer Lynch	Mar 19, 2019, 12 PM

Assuming I'm searching for "Alex Smith" in column A and "Mar 19, 2019*" in column B, this is what I expect my results to look like:

Results for Alex Smith:

Mar 19, 2019, 7 AM

Mar 19, 2019, 7 AM

Mar 19, 2019, 7 AM

Mar 19, 2019, 9 AM

Mar 19, 2019, 9 AM

Mar 19, 2019, 1 PM

I've gotten really close using the formula below, but unfortunately the wildcard doesn't work. ($A$2 is referencing a cell that includes "Mar 19, 2019".)

Please Login or Register  to view this content.

Thank you ahead of time for any and all help!

[CARACALLA]

	A	B	C	D
1	name	Timestamp
2	Alex Smith	Mar 18, 2019, 7 AM		Mar 19, 2019, 7 AM
3	Alex Smith	Mar 18, 2019, 7 AM		Mar 19, 2019, 7 AM
4	Alex Smith	Mar 18, 2019, 8 AM		Mar 19, 2019, 7 AM
5	Alex Smith	Mar 18, 2019, 9 AM		Mar 19, 2019, 9 AM
6	Alex Smith	Mar 18, 2019, 12 PM		Mar 19, 2019, 9 AM
7	Alex Smith	Mar 18, 2019, 12 PM		Mar 19, 2019, 1 PM
8	Alex Smith	Mar 19, 2019, 7 AM
9	Alex Smith	Mar 19, 2019, 7 AM
10	Alex Smith	Mar 19, 2019, 7 AM
11	Alex Smith	Mar 19, 2019, 9 AM
12	Alex Smith	Mar 19, 2019, 9 AM
13	Alex Smith	Mar 19, 2019, 1 PM
14	Jennifer Lynch	Mar 18, 2019, 7 AM
15	Jennifer Lynch	Mar 18, 2019, 8 AM
16	Jennifer Lynch	Mar 19, 2019, 7 AM
17	Jennifer Lynch	Mar 19, 2019, 12 PM

D2=IFERROR(INDEX($B$2:$B$17,SMALL(IF($A$2:$A$17=$A$2,IF(ISNUMBER(SEARCH("Mar 19, 2019,",$B$2:$B$17)),ROW($B$2:$B$17)-ROW($B$2)+1)),ROWS($B$2:B2))),"")

control+shift +enter

copy down

[jason.b75]

If you have proper dates formatted as 'mmm dd, yyyy, h AM/PM then try

Please Login or Register  to view this content.

If they are text strings then try

Please Login or Register  to view this content.

$D$1 = name to search for
$D$2 = wildcard date
E$2:E2 = cell that you enter the formula into before filling down.

[rachelsteele]

Both worked perfectly. Thank you!