Need help. I think the OFFSET is the correct function but I don’t have the correct imbedded formulas.

[Willingtolearn]

Here is my situation. I am trying to use a list of employees in one worksheet (portfolio) to identify the correct data range in another worksheet (Output) that is associated with their name. From this range I need to collect current salary, company 401k Match and Company Paid Health Insurance and place this data in the portfolio worksheet next the employees name. I know OFFSET would enable me to find the correct cell in the Output worksheet, using the cell with the employee's name as the starting point. My issue is I cannot figure out how to use the list on the portfolio worksheet to search for the same employee name in the Output worksheet and return the correct data. One other issue is that the data in the Output worksheet is not fixed month to month. It is imported data and if there are new employees or resigned employees the data shifts in the worksheet. For example if a new employee is hired, named Albert, his information will be imported into the Output worksheet before John and other employees.

Can anyone help me with the correct formula to incorporate the required data into the respective cells in the Portfolio worksheet?

EMP Ben Test.xlsx
	A	B	C	D	E	F	G
1
2
3			Employee		Current Pay	Co. Paid Health Insurance	Co. Match
4			John		0
5			Nancy		2020 April
6			Roger
7			Sally
8			Jill
9			Jack
10			Allen
11			Tony
12			Seymour
13
14
15
16
17
18
Portfolio

Cell Formulas
Range	Formula
E4:E5	E4	=OFFSET(Output!A$2,MATCH(C4,Output!$2:$2,0)+3-1,2)

EMP Ben Test.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1
2		Employee:	John									Employee:	Nancy									Employee:	Roger
3
4				Starting Salary:	$60,000			Company Paid Health Insurance:	$2,208					Starting Salary:	$50,000			Company Paid Health Insurance:	$2,928					Starting Salary:	$35,000			Company Paid Health Insurance:	$2,208
5				Current Salary :	$71,250			Current Company 401K Match:	$3,563					Current Salary :	$81,917			Current Company 401K Match:	$4,096					Current Salary :	$36,250			Current Company 401K Match:	$1,813
6				Potential Salary :	$75,000									Potential Salary :	$85,000									Potential Salary :	$38,000
7
8
9				Health Care	401k Match	Pay								Health Care	401k Match	Pay								Health Care	401k Match	Pay
10		2020 December		$184	$312.50	$6,250.00						2020 December		$244	$358.33	$7,166.67						2020 December		$184	$158.33	$3,166.67
11		2020 November		$184	$312.50	$6,250.00						2020 November		$244	$358.33	$7,166.67						2020 November		$184	$158.33	$3,166.67
12		2020 October		$184	$312.50	$6,250.00						2020 October		$244	$358.33	$7,166.67						2020 October		$184	$158.33	$3,166.67
13		2020 September		$184	$291.67	$5,833.33						2020 September		$244	$358.33	$7,166.67						2020 September		$184	$158.33	$3,166.67
14		2020 August		$184	$291.67	$5,833.33						2020 August		$244	$358.33	$7,166.67						2020 August		$184	$158.33	$3,166.67
15		2020 July		$184	$291.67	$5,833.33						2020 July		$244	$329.17	$6,583.33						2020 July		$184	$145.83	$2,916.67
16		2020 June		$184	$291.67	$5,833.33						2020 June		$244	$329.17	$6,583.33						2020 June		$184	$145.83	$2,916.67
17		2020 May		$184	$291.67	$5,833.33						2020 May		$244	$329.17	$6,583.33						2020 May		$184	$145.83	$2,916.67
18		2020 April		$184	$291.67	$5,833.33						2020 April		$244	$329.17	$6,583.33						2020 April		$184	$145.83	$2,916.67
19		2020 March		$184	$291.67	$5,833.33						2020 March		$244	$329.17	$6,583.33						2020 March		$184	$145.83	$2,916.67
20		2020 February		$184	$291.67	$5,833.33						2020 February		$244	$329.17	$6,583.33						2020 February		$184	$145.83	$2,916.67
21		2020 January		$184	$291.67	$5,833.33						2020 January		$244	$329.17	$6,583.33						2020 January		$184	$145.83	$2,916.67
22
23
24
25
26
27
28
29
Output

Cell Formulas
Range	Formula
F5,Z5,P5	F5	=SUM(G10:G21)
J4,AD4,T4	J4	=SUM(E10:E21)
J5,AD5,T5	J5	=SUM(F10:F21)
Z10:Z21,P10:P21,F10:F21	F10	=0.05*G10

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1
2
3			Employee		Current Pay	Co. Paid Health Insurance	Co. Match
4			John		71250	2208	3562.5
5			Nancy		81916.67	2928	4095.833
6			Roger		36250	2208	1812.5
7			Sally
8			Jill
9			Jack
10			Allen
11			Tony
12			Seymour
13
Lists

Cell Formulas
Range	Formula
E4:E12	E4	=IFNA(INDEX(Output!$A$5:$AH$5,MATCH($C4,Output!$A$2:$AH$2,0)+2),"")
F4:F12	F4	=IFNA(INDEX(Output!$A$4:$AH$4,MATCH($C4,Output!$A$2:$AH$2,0)+6),"")
G4:G12	G4	=IFNA(INDEX(Output!$A$5:$AH$5,MATCH($C4,Output!$A$2:$AH$2,0)+6),"")

 

[Willingtolearn]

Thank you Fluff. That was what I was looking for to solve my issue. I wasn't thinking about nested Index and match.

 

[Fluff]

You're welcome & thanks for the feedback.