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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]ID[/TD]
[TD="align: center"]Date/Time[/TD]
[TD="align: center"]Event[/TD]
[TD="align: center"]Description[/TD]
[/TR]
[TR]
[TD]E1[/TD]
[TD]3/19/18 16:11[/TD]
[TD]Account Created[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E1[/TD]
[TD]3/20/18 16:11[/TD]
[TD]Logged out[/TD]
[TD]Timeout[/TD]
[/TR]
[TR]
[TD]E1[/TD]
[TD]3/21/18 16:11[/TD]
[TD]Logged out[/TD]
[TD]Timeout[/TD]
[/TR]
[TR]
[TD]E1[/TD]
[TD]3/22/18 16:11[/TD]
[TD]Logged out[/TD]
[TD]Transfer[/TD]
[/TR]
[TR]
[TD]E1[/TD]
[TD]3/23/18 16:11[/TD]
[TD]Logged out[/TD]
[TD]Transfer[/TD]
[/TR]
[TR]
[TD]E1[/TD]
[TD]3/24/18 16:11[/TD]
[TD]Logged out[/TD]
[TD]Transfer[/TD]
[/TR]
[TR]
[TD]E2[/TD]
[TD]3/19/18 16:11[/TD]
[TD]Account Created[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E2[/TD]
[TD]3/24/18 16:11[/TD]
[TD]Logged out[/TD]
[TD]Transfer[/TD]
[/TR]
[TR]
[TD]E2[/TD]
[TD]3/26/18 16:11[/TD]
[TD]Logged out[/TD]
[TD]Transfer[/TD]
[/TR]
[TR]
[TD]E2[/TD]
[TD]3/27/18 16:11[/TD]
[TD]Logged out[/TD]
[TD]Transfer[/TD]
[/TR]
[TR]
[TD]E3[/TD]
[TD]3/20/18 16:11[/TD]
[TD]Account Created[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E3[/TD]
[TD]3/21/18 16:11[/TD]
[TD]Logged out[/TD]
[TD]Timeout[/TD]
[/TR]
[TR]
[TD]E3[/TD]
[TD]3/27/18 16:11[/TD]
[TD]Logged out[/TD]
[TD]Transfer[/TD]
[/TR]
[TR]
[TD]E4[/TD]
[TD]3/29/18 16:11[/TD]
[TD]Account Created[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E4[/TD]
[TD]4/27/18 16:11[/TD]
[TD]Logged out[/TD]
[TD]Transfer[/TD]
[/TR]
[TR]
[TD]E4[/TD]
[TD]5/02/18 16:11[/TD]
[TD]Logged out[/TD]
[TD]Transfer[/TD]
[/TR]
[TR]
[TD]E4[/TD]
[TD]5/03/18 16:11[/TD]
[TD]Logged out[/TD]
[TD]Transfer[/TD]
[/TR]
[TR]
[TD]E4[/TD]
[TD]5/04/18 16:11[/TD]
[TD]Logged out[/TD]
[TD]Transfer[/TD]
[/TR]
</tbody>[/TABLE]

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!