Formula Help...First instance of a string based on another variable.

[larsocha]

Hello,

What I want to do is find the first instance of a "string" contained within a cell based on a grouping of other cells and return another cell...

Looking at the table below, I want to find the first "Transfer" in column D for the group of specified ID's in Column A and return the value in Column B. So...E1 would return 3/22/18 16:11, E2 return 3/24/18 16:11, E3 return 3/27/18 16:11 and E4 return 4/27/18 16:11.

ID	Date/Time	Event	Description
E1	3/19/18 16:11	Account Created
E1	3/20/18 16:11	Logged out	Timeout
E1	3/21/18 16:11	Logged out	Timeout
E1	3/22/18 16:11	Logged out	Transfer
E1	3/23/18 16:11	Logged out	Transfer
E1	3/24/18 16:11	Logged out	Transfer
E2	3/19/18 16:11	Account Created
E2	3/24/18 16:11	Logged out	Transfer
E2	3/26/18 16:11	Logged out	Transfer
E2	3/27/18 16:11	Logged out	Transfer
E3	3/20/18 16:11	Account Created
E3	3/21/18 16:11	Logged out	Timeout
E3	3/27/18 16:11	Logged out	Transfer
E4	3/29/18 16:11	Account Created
E4	4/27/18 16:11	Logged out	Transfer
E4	5/02/18 16:11	Logged out	Transfer
E4	5/03/18 16:11	Logged out	Transfer
E4	5/04/18 16:11	Logged out	Transfer

<tbody>
</tbody>

Any help would be appreciated...Thanks!

 

[Special-K99]

In Sheet2 column A put the E values E1 to E4

In Sheet2!B1
Array formula
=MIN(IF((Sheet1!D1:D1000="Transfer")*(Sheet1!A1:A1000=A1),Sheet1!B1:B1000))
and copy down the column

 

[larsocha]

This is working so for...one follow-up question.

Is there a way to make the ="Transfer" section a variable string that contains "Transfer"? Something like ="*transfer*"?

Thanks.

 

[Special-K99]

Array formula again
=MIN(IF(ISNUMBER(SEARCH("Transfer",Sheet1!D1:D1000))*(Sheet1!A1:A1000=A1),Sheet1!B1:B1000))

 

[larsocha]

Working well.

Thank you very much!