XLookup or Match/Index? Unsure which to use

[Redd_Dayspring]

Good evening. I have been struggling with this, I'm not sure if I'm on the right track.
I have two workbooks - workbook 2 has accrual amounts by date and employee ID that I need to populate into workbook 1.
Is there a way to match the week ending dates (C) and employee ID's (E) from workbook 1 to the damage dates (A) and employee ID's (B) from workbook 2 and place the corresponding accrual amount into workbook 1 column G.
I've highlighted where they should go and the correct amount in yellow.

Everything I've come up with has either resulted in an error or the incorrect amount.
Any help, direction, or resources on where to learn how to figure this out would be greatly appreciated.
Unfortunately, due to security settings, I can only attach a picture vs the mini sheet.
Thank you in advance.

 

[Peter_SSs]

I can only attach a picture vs the mini sheet.

Another option is to copy/paste directly from your worksheet to the forum and tell us the range. It is not as good as XL2BB but at least we can copy from it.

I know my dates are not realistic but see if this helps.

Workbook 2.xlsx
	A	B				F
1	Damage Date	Employee ID				Accrual Amount
2	5/01/2023	123456				1
3	25/01/2023	987654				2
4	14/02/2023	123456				3
5	6/03/2023	123456				4
6	26/03/2023	123456				5
7	15/04/2023	123456				6
8	5/05/2023	123456				7
9	25/05/2023	123456				8
10	14/06/2023	123456				9
Sheet1

Workbook 1.xlsx
	C		E		G
1	Week Ending		Employee ID		Accrual Amount
2	7/01/2023		123456
3	14/02/2023		123456		3
4	23/02/2023		123456
5	16/03/2023		123456
6	25/01/2023		123456
7	15/04/2023		123456		6
8	5/05/2023		123456		7
9	25/01/2023		987654		2
10	15/02/2023		987654
Sheet1

Cell Formulas
Range	Formula
G2:G10	G2	=FILTER('[Workbook 2.xlsx]Sheet1'!F$2:F$100,('[Workbook 2.xlsx]Sheet1'!A$2:A$100=C2)*('[Workbook 2.xlsx]Sheet1'!B$2:B$100=E2),"")

 

[Alex Blakenburg]

So many missing pieces.
• In Workbook 1 you have the same dates repeating for the same employee ID, how is the formula to know which of the repeating dates to return a value to.
• I assume the return value of $ 947.17 is an error since that is from the Invoice column not the Accrual column
• In Workbook 2 all the values are left aligned, does that mean they are all being read as Text by Excel instead of dates and numbers ?
(If you are not sure change the number format, if nothing appears to change it is text, this will change how the formula needs to work)
• Since workbook 1 is in weeks I assume you could return more than one value which needs to be totalled.

Here is a variation of @Peter_SSs's formula catering for some of those.
I tried it on a closed workbook and it appears to work (SumIfs did not)

=LET(fltr,FILTER('[Workbook 2.xlsx]Sheet1'!F$2:F$101,('[Workbook 2.xlsx]Sheet1'!A$2:A$101<=C2)*('[Workbook 2.xlsx]Sheet1'!A$2:A$101>(C2-7))*('[Workbook 2.xlsx]Sheet1'!B$2:B$101=E2),0),IF(SUM(fltr)=0,"",SUM(fltr)))

 

[Redd_Dayspring]

My apologies - I'm trying to find a way to upload a model of what I am working with while redacting sensitive information and not being able to use XL2BB and post as a mini sheet. I honestly appreciate the help and direction. I'm trying both posted solutions.