Need to find data within a specified range when there are multiple ranges with similar data

[LoganAltsEval]

I have a spreadsheet of payroll data from a payroll provider. The data has each employee's name and then specific payroll info (such as gross pay, employer taxes, employee taxes, etc.) in multiple columns to the right of each person. Sometimes the order of the employees stays the same and other times it changes (i.e. an employee is added or removed). The data in the columns may also change based on specific items related to that employee in a given pay period. For example, one pay period they may have mileage reimbursement but the next pay period they do not.

I would like to write a formula that identifies the employee listed in column A, then finds the specific type of info I am looking for (such as total taxes), then returns the amount in the cell immediately to the right of that info. For example, in the attached screenshot (names changed to protect privacy), the formula would first search column A for "Sweat, Bill", then it would search his payroll info to the right (cells A13:S23) for the words "Total Taxes" (in merged cell I22), then it would return the value of 515.67 (in merged cell J22). Is there a formula for this?

Note that this info is downloaded from our payroll provider, pasted into another spreadsheet that has additional tabs and linked formulas, and is utilized by my staff who are not proficient with Excel, so I do not have the flexibility or desire to change the formatting of the info.

 

[KRice]

Here is an abbreviated source table:

MrExcel_20231228.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Doe, John
2
3
4
5							Med	1.11
6
7
8
9
10				Total Earnings	1				Total Taxes	2
11
12
13	Sweat, Bill
14
15
16							SS	2.22
17
18
19
20
21
22				Total Earnings	3				Total Taxes	4
23
24
25	Williams, Jane
26
Sheet1

...and an idea for extracting the value of interest:

MrExcel_20231228.xlsx
	A	B	C
1
2	Sweat, Bill	Total Taxes	4
3	Doe, John	Med	1.11
4	Sweat, Bill	SS	2.22
Sheet2

Cell Formulas
Range	Formula
C2:C4	C2	=LET(ur,MATCH(A2,Sheet1!$A$1:$A$1000,0),lr,MATCH("Total Earnings",INDEX(Sheet1!D:D,ur+1):Sheet1!D1000,0)+ur+1,rng,INDEX(Sheet1!A:A,ur):INDEX(Sheet1!S:S,lr),rng2col,TOCOL(rng),INDEX(rng2col,MATCH(B2,rng2col,0)+1))

This method assumes the words "Total Earnings" in column D can be used to identify the bottom of a person's information block. I'm not sure whether the merged cells (generally a bad idea) will cause any problems, so test carefully.

 

[KRice]

Same idea with a slightly shorter formula (in col D) for creating the range to be searched:

MrExcel_20231228.xlsx
	A	B	C	D
2	Sweat, Bill	Total Taxes	4	4
Sheet2

Cell Formulas
Range	Formula
C2	C2	=LET(ur,MATCH(A2,Sheet1!$A$1:$A$1000,0),lr,MATCH("Total Earnings",INDEX(Sheet1!D:D,ur+1):Sheet1!D1000,0)+ur+1,rng,INDEX(Sheet1!A:A,ur):INDEX(Sheet1!S:S,lr),rng2col,TOCOL(rng),INDEX(rng2col,MATCH(B2,rng2col,0)+1))
D2	D2	=LET(ur,MATCH(A2,Sheet1!$A$1:$A$1000,0),lr,MATCH("Total Earnings",DROP(Sheet1!D:D,ur-1),0)+ur,rng,TAKE(DROP(Sheet1!A:S,ur-1),lr-ur+1),rng2col,TOCOL(rng),INDEX(rng2col,MATCH(B2,rng2col,0)+1))

 

[LoganAltsEval]

That worked! Thank you both.

 

[KRice]

You’re welcome…happy to help.