I want an ArrayFormula for Index Match or Filter formula

[asolopreneur]

TASK	TASK ID	ASSIGNED TO
TASK 1	200623175025	ADAM
TASK 2	200623175159	JONAS
TASK 3	200623184657	JAMES
TASK 4	200623184918	ROSSI
TASK 5	210623180111	WILLIAN
TASK 6	210623191226	JONAS
TASK 7	230623151100	JONAS

SHEET 1 - TASK GENERATED BY Google FORM, Which we can report as complete via another Google form

--------------------------------------

Timestamp	BY	TASK ID
26/06/2023 13:52:17	ADAM	200623175025
26/06/2023 17:39:12	JONAS	200623175159
26/06/2023 18:49:11	JONAS	210623191226
26/06/2023 19:07:20	JONAS	230623151100
26/06/2023 19:08:29	WILLIAN	210623180111

SHEET 2 - Task completed Generated by Google Form

--
Now I want Timestamps (Column A) in "D" Column in Sheet1. I want to match task ID and get timestamp. I am using filter formula right now but I need to drag the formula because Google form inserts new row whenever new entry appears.


That's why I need to make Filter formula arrayformula or I need vlookup or any other formula to make it arrayformula so any time new entry appears I don't need to drag formula.

 

[Bosquedeguate]

Do you what a Google sheets solution or an Excel solution?

 

[Bosquedeguate]

How is your data entered into Sheet1?

I assume from your example that Sheet2 that this data might be coming from a Google form? If not how is Sheet 2 data entered?

You could write Google Scripts code to automatically put entries into Sheet1 when an entry is made in Sheet2?

Please explain the process. Also, when and how you want to update Sheet1 data.

 

[Bosquedeguate]

Try this function in the cells where you want the TIMESTAMP value from another sheet.

My target sheet was Sheet1. The sheet with Timestamps was Sheet2.

=IF(ISNA(XLOOKUP(B2,Sheet2!C:C,Sheet2!A:A)),"",XLOOKUP(B2,Sheet2!C:C,Sheet2!A:A))

 

[asolopreneur]

Try this function in the cells where you want the TIMESTAMP value from another sheet.

My target sheet was Sheet1. The sheet with Timestamps was Sheet2.

=IF(ISNA(XLOOKUP(B2,Sheet2!C:C,Sheet2!A:A)),"",XLOOKUP(B2,Sheet2!C:C,Sheet2!A:A))

thanks a lot, it worked.

 

[Peter_SSs]

thanks a lot, it worked.

Then wouldn't this simpler one also work for you?

asolopreneur.xlsm
	A	B	C
1	Timestamp	BY	TASK ID
2	26/06/2023 13:52	ADAM	200623175025
3	26/06/2023 17:39	JONAS	200623175159
4	26/06/2023 18:49	JONAS	210623191226
5	26/06/2023 19:07	JONAS	230623151100
6	26/06/2023 19:08	WILLIAN	210623180111
Sheet2

asolopreneur.xlsm
	A	B	C	D
1	TASK	TASK ID	ASSIGNED TO	Timestamp
2	TASK 1	200623175025	ADAM	26/06/2023 13:52
3	TASK 2	200623175159	JONAS	26/06/2023 17:39
4	TASK 3	200623184657	JAMES
5	TASK 4	200623184918	ROSSI
6	TASK 5	210623180111	WILLIAN	26/06/2023 19:08
7	TASK 6	210623191226	JONAS	26/06/2023 18:49
8	TASK 7	230623151100	JONAS	26/06/2023 19:07
Sheet1

Cell Formulas
Range	Formula
D2:D8	D2	=XLOOKUP(B2,Sheet2!C:C,Sheet2!A:A,"")