Look up 3 criteria to give a result if date falls within a week ending period

[marshak]

Hello all, hope that everyone is well.

I need your assistance, once again.

In the attached spreadsheet: tab, WEEKLY ADJUSTMENT SCH, contains a column that I have to fill in (column L), based on the data that appears in Tab, 2019.

I need to look for a match of three columns (from tab, Weekly...) and return a value found under the correct week ending date (tab, 2019).

For example, I need a formula to place in cell L4 (tab Weekly...), that will first find/match:

LAST NAME (column a)​

LAST 4 SSN (column E)​

EFFECTIVE WEEKS TO (column G)​

in tab 2019.

If there is an exact match of these three criterias in tab 2019, the formula needs to result in the value under the correct Week Ending date (this data found in row 5). If there is no match, it needs to show "NO MATCH"

Please let me know if you have any questions.

Thanks you so much for your help!


This is the WEEKLY ADJUSTMENT SCH worksheet:

TEST.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1				SOCIAL
2				SECURITY	last 4	EFFECTIVE WEEKS			WEEKS		PENSION	Weekly
3	LAST NAME	FIRST NAME	M.I.	NUMBER	of SSN	FROM	TO		DUE		AMOUNT	Hours
4	Xaviar	Roger	A		0235	02/03/19	02/09/19
5	Xaviar	Roger	A		0235	02/10/19	02/16/19
6	Xaviar	Roger	A		0235	02/17/19	02/23/19
7	Xaviar	Roger	A		0235	02/24/19	03/02/19
8	Xaviar	Roger	A		0235	03/03/19	03/09/19
9	Xaviar	Roger	A		0235	03/10/19	03/16/19
10	Xaviar	Roger	A		0235	03/17/19	03/23/19
11	Xaviar	Roger	A		0235	03/24/19	03/30/19
12	Xaviar	Roger	A		0235	03/31/19	04/06/19
13	Xaviar	Roger	A		0235	04/07/19	04/13/19
14	Xaviar	Roger	A		0235	04/14/19	04/20/19
15	Xaviar	Roger	A		0235	04/21/19	04/27/19					1
16	Xaviar	Roger	A		0235	04/28/19	05/04/19					1
17	Xaviar	Roger	A		0235	05/05/19	05/11/19					1
18	Xaviar	Roger	A		0235	05/12/19	05/18/19					1
19	Xaviar	Roger	A		0235	05/19/19	05/25/19					1
20	Xaviar	Roger	A		0235	05/26/19	06/01/19					1
21	Xaviar	Roger	A		0235	06/02/19	06/08/19					1
22	Xaviar	Roger	A		0235	06/09/19	06/15/19					1
23	Xaviar	Roger	A		0235	06/16/19	06/22/19					1
24	Xaviar	Roger	A		0235	06/23/19	06/29/19					1
25	Xaviar	Roger	A		0235	06/30/19	07/06/19					1
26	Xaviar	Roger	A		0235	07/07/19	07/13/19					1
27	Xaviar	Roger	A		0235	07/14/19	07/20/19					1
28	Xaviar	Roger	A		0235	07/21/19	07/27/19					VAC
29	Xaviar	Roger	A		0235	07/28/19	08/03/19					VAC
30	Xaviar	Roger	A		0235	08/04/19	08/10/19
31	Xaviar	Roger	A		0235	08/11/19	08/17/19
32	Xaviar	Roger	A		0235	08/18/19	08/24/19
33	Xaviar	Roger	A		0235	08/25/19	08/31/19
34	Xaviar	Roger	A		0235	09/01/19	09/07/19
35	Xaviar	Roger	A		0235	09/08/19	09/14/19
WEEKLY ADJUSTMENT SCH

This is the 2019 worksheet:

TEST.xlsx
	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	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK
1
2										Week Ending
3	File #	Name				SSN	LAST 4	Hire Date		5-Jan	12-Jan	19-Jan	26-Jan	2-Feb	9-Feb	16-Feb	23-Feb	2-Mar	9-Mar	16-Mar	23-Mar	30-Mar	6-Apr	13-Apr	20-Apr	27-Apr	4-May	11-May	18-May	25-May	1-Jun	8-Jun	15-Jun	22-Jun	29-Jun	6-Jul	13-Jul	20-Jul	27-Jul	3-Aug	10-Aug	17-Aug	24-Aug	31-Aug	7-Sep	14-Sep	21-Sep	28-Sep	5-Oct	12-Oct	19-Oct	26-Oct	2-Nov	9-Nov	16-Nov	23-Nov	30-Nov	7-Dec	14-Dec	21-Dec	28-Dec
4			LAST NAME	FIRST NAME	MI		OF SSN
5	500	Xaviar, Roger	Xaviar	Roger	A		0235			1	1	1	1	VAC	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	VAC	VAC	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	VAC	VAC
6
7
8
9
10
11
12
2019

 

[duggie33]

What version of Excel are you using?

Doug