VLOOKUP Maybe???

[IsItFriday]

Hello,

I am attempting to make a long drawn-out process at work a bit simpler however I am running into a few issues.
I'm somewhat new to excel so please be patient with me if I do not understand right away or if my questions are elementary.

I am pulling information from a report and dumping it into a tab in my workbook.
My goal is to have specific numbers autofill into a cell based on a specific string of text. (Employee ID)
Normally a vlookup formula would be my go-to however there are multiple balances in the cell I am needing to pull from. (I am probably horrible at explaining this.)

In the table listed below I am wanting the last available running balance.
In another cell I am needing the "Balance Forward" balance.
Then in a different cell I am needing the total sum of negative Vacation hours or what is in the total debits.

So far I have =VLOOKUP(AB5,'Accrual Detail'!A8463:H8491,8,FALSE)

However it is giving me the -7.17 since that is the first value.

Any advice or help would be greatly appreciated!!!

Employee ID	Employee	Accrual Code	Effective Date	Action	Amount	Amount Flag	Running Balance
7353270​	Smith, John Will	PTO (Hours)	Fri 12/30/2022	Balance Forward			-7.17
7353270​	Smith, John Will	PTO (Hours)	Fri 1/13/2023	Earned	1.38		-5.78
7353270​	Smith, John Will	PTO (Hours)	Fri 1/13/2023	Earned	0.00		-5.78
7353270​	Smith, John Will	PTO (Hours)	Fri 1/27/2023	Earned	1.38		-4.40
7353270​	Smith, John Will	PTO (Hours)	Fri 1/27/2023	Earned	0.00		-4.40
7353270​	Smith, John Will	PTO (Hours)	Fri 2/10/2023	Earned	0.00		-4.40
7353270​	Smith, John Will	PTO (Hours)	Fri 2/10/2023	Earned	1.38		-3.02
7353270​	Smith, John Will	PTO (Hours)		Total Debits	0.00
7353270​	Smith, John Will	PTO (Hours)		Total Credits	4.15
7353270​	Smith, John Will	Vacation (Hours)	Fri 12/30/2022	Balance Forward			3.33
7353270​	Smith, John Will	Vacation (Hours)	Sun 1/1/2023	Earned	80.00		83.33
7353270​	Smith, John Will	Vacation (Hours)	Fri 1/13/2023	Earned	0.00		83.33
7353270​	Smith, John Will	Vacation (Hours)	Fri 1/20/2023	Taken	-12.00		71.33
7353270​	Smith, John Will	Vacation (Hours)	Sat 1/21/2023	Taken	-12.00		59.33
7353270​	Smith, John Will	Vacation (Hours)	Sun 1/22/2023	Taken	-12.00		47.33
7353270​	Smith, John Will	Vacation (Hours)	Fri 1/27/2023	Earned	0.00		47.33
7353270​	Smith, John Will	Vacation (Hours)	Fri 1/27/2023	Taken	-12.00		35.33
7353270​	Smith, John Will	Vacation (Hours)	Sat 1/28/2023	Taken	-12.00		23.33
7353270​	Smith, John Will	Vacation (Hours)	Sun 1/29/2023	Taken	-12.00		11.33
7353270​	Smith, John Will	Vacation (Hours)	Fri 2/10/2023	Earned	0.00		11.33
7353270​	Smith, John Will	Vacation (Hours)		Total Debits	72.00
7353270​	Smith, John Will	Vacation (Hours)		Total Credits	80.00
7353270​	Smith, John Will	Vacation Accrued (Hours)	Fri 12/30/2022	Balance Forward			2.77
7353270​	Smith, John Will	Vacation Accrued (Hours)	Sun 1/1/2023	Adjust Carryover Limit	-2.77		0.00
7353270​	Smith, John Will	Vacation Accrued (Hours)	Fri 1/13/2023	Earned	2.77		2.77
7353270​	Smith, John Will	Vacation Accrued (Hours)	Fri 1/27/2023	Earned	2.77		5.53
7353270​	Smith, John Will	Vacation Accrued (Hours)	Fri 2/10/2023	Earned	2.77		8.30
7353270​	Smith, John Will	Vacation Accrued (Hours)		Total Debits	0.00
7353270​	Smith, John Will	Vacation Accrued (Hours)		Total Credits	8.30

 

[Joe4]

In order to get the latest running balance, I think you should be able to use the MAXIFS function, as long as your values in your date column are REALLY entered as dates and not as text.
See: MAXIFS function - Microsoft Support

To get the total sum of Vacation hours, you can probably use the SUMIFS function, which allows you to sum multiple values meeting certain criteria.
See: SUMIFS function - Microsoft Support

 

[Alex Blakenburg]

Not fully understanding your requirement but see if this is of any help.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Employee ID	Employee	Accrual Code	Effective Date	Action	Amount	Amount Flag	Running Balance					Xlookup	Alternative
2	7353270	Smith, John Will	PTO (Hours)	Fri 12/30/2022	Balance Forward			-7.17		Employee ID	Accrual Code	Action	Amount	Amount
3	7353270	Smith, John Will	PTO (Hours)	Fri 1/13/2023	Earned	1.38		-5.78		7353270	PTO (Hours)		-3.02
4	7353270	Smith, John Will	PTO (Hours)	Fri 1/13/2023	Earned	0		-5.78		7353270	Vacation (Hours)	Total Debits	72.00	72.00
5	7353270	Smith, John Will	PTO (Hours)	Fri 1/27/2023	Earned	1.38		-4.4
6	7353270	Smith, John Will	PTO (Hours)	Fri 1/27/2023	Earned	0		-4.4
7	7353270	Smith, John Will	PTO (Hours)	Fri 2/10/2023	Earned	0		-4.4
8	7353270	Smith, John Will	PTO (Hours)	Fri 2/10/2023	Earned	1.38		-3.02
9	7353270	Smith, John Will	PTO (Hours)		Total Debits	0
10	7353270	Smith, John Will	PTO (Hours)		Total Credits	4.15
11	7353270	Smith, John Will	Vacation (Hours)	Fri 12/30/2022	Balance Forward			3.33
12	7353270	Smith, John Will	Vacation (Hours)	Sun 1/1/2023	Earned	80		83.33
13	7353270	Smith, John Will	Vacation (Hours)	Fri 1/13/2023	Earned	0		83.33
14	7353270	Smith, John Will	Vacation (Hours)	Fri 1/20/2023	Taken	-12		71.33
15	7353270	Smith, John Will	Vacation (Hours)	Sat 1/21/2023	Taken	-12		59.33
16	7353270	Smith, John Will	Vacation (Hours)	Sun 1/22/2023	Taken	-12		47.33
17	7353270	Smith, John Will	Vacation (Hours)	Fri 1/27/2023	Earned	0		47.33
18	7353270	Smith, John Will	Vacation (Hours)	Fri 1/27/2023	Taken	-12		35.33
19	7353270	Smith, John Will	Vacation (Hours)	Sat 1/28/2023	Taken	-12		23.33
20	7353270	Smith, John Will	Vacation (Hours)	Sun 1/29/2023	Taken	-12		11.33
21	7353270	Smith, John Will	Vacation (Hours)	Fri 2/10/2023	Earned	0		11.33
22	7353270	Smith, John Will	Vacation (Hours)		Total Debits	72
23	7353270	Smith, John Will	Vacation (Hours)		Total Credits	80
24	7353270	Smith, John Will	Vacation Accrued (Hours)	Fri 12/30/2022	Balance Forward			2.77
25	7353270	Smith, John Will	Vacation Accrued (Hours)	Sun 1/1/2023	Adjust Carryover Limit	-2.77		0
26	7353270	Smith, John Will	Vacation Accrued (Hours)	Fri 1/13/2023	Earned	2.77		2.77
27	7353270	Smith, John Will	Vacation Accrued (Hours)	Fri 1/27/2023	Earned	2.77		5.53
28	7353270	Smith, John Will	Vacation Accrued (Hours)	Fri 2/10/2023	Earned	2.77		8.3
29	7353270	Smith, John Will	Vacation Accrued (Hours)		Total Debits	0
30	7353270	Smith, John Will	Vacation Accrued (Hours)		Total Credits	8.3
Sheet1

Cell Formulas
Range	Formula
M3	M3	=XLOOKUP(1,($A$2:$A$30=J3)*($C$2:$C$30=K3)*($H$2:$H$30<>""),$H$2:$H$30,"",0,-1)
M4	M4	=XLOOKUP(1,($A$2:$A$30=J4)*($C$2:$C$30=K4)*($E$2:$E$30="Total Debits"),$F$2:$F$30,"")
N4	N4	=SUMIFS($F$2:$F$30,$A$2:$A$30,J4,$C$2:$C$30,K4,$E$2:$E$30,L4)