Help with Lookup

[raccoon588]

Column A has a full year of dates. Column L has the number I need. I need to find the last populated date for each month and pull the value from column L. (Some may end on the 20th or some the 31st....etc) I was using =lookup but it doesn't seem to be getting there for me.

 

[DanteAmor]

Try this,
In the following example I put the month in row 1 (as date day 1 of each month) and the values in row 2.

Book1
	A											L	M	N	O	P	Q	R
1													Month	01/05/2019	01/06/2019	01/07/2019	01/08/2019	01/09/2019
2													Value	195	285	375	429	Not found
3
4	DATE											VALUES
5	04/05/2019											123
6	10/05/2019											141
7	16/05/2019											159
8	22/05/2019											177
9	28/05/2019											195
10	03/06/2019											213
11	09/06/2019											231
12	15/06/2019											249
13	21/06/2019											267
14	27/06/2019											285
15	03/07/2019											303
16	09/07/2019											321
17	15/07/2019											339
18	21/07/2019											357
19	27/07/2019											375
20	02/08/2019											393
21	08/08/2019											411
22	14/08/2019											429
23
Hoja2

Cell Formulas
Range	Formula
N2	N2	=IFERROR(LOOKUP(2,1/(MONTH(N1)=MONTH($A$5:$A$22)),$L$5:$L$22),"Not found")

 

[raccoon588]

It only returns false and true for me

 

[DanteAmor]

You can copy the complete example on an excel sheet.
Check that you have complete dates in row 1 and column A.
Copy all my example and you will see how the formula works.
Or write here how you have your data on your sheet, because otherwise I'm just assuming.

 

[raccoon588]

I would need to show the number in the the variance (MTD) column. so for October 7951 and for nov so far 3613. the formula goes into the following table which resides on a different tab in the workbook Each column represent a different tab in the workbook that houses the raw data. the raw data is sent up the same on each tab like the second set of cells i show bellow:

	BdMOLB	BdMoHB	H13Cap	H13Assem	H8Cap	9007Assem	Coating	Ablation	Goal
Oct	76972	27120	7951	-94476	-89609	44846	-12819	-130801	0​
Nov	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	0​
Dec	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	0​
Jan	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	0​
Feb	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	0​
Mar	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	0​
Apr	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	0​
May	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	0​
Jun	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	0​
Jul	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	0​
Aug	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	0​
Sep	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	0​

Date	Shifts Worked	Shifts Planned	Shifts Worked (MTD)	3rd Shift	1st Shift	2nd Shift	Total (Day)	Total (MTD)	Goal (Day)	Goal (MTD)	Varrience (MTD)	Average (MTD)
10/1/2019	2	2	2	896	11,998	14,595	27,489	27,489	24,448	24,448	3,041	27,489
10/2/2019	2	2	4	944	13,290	13,291	27,525	55,014	24,448	48,896	6,118	27,507
10/3/2019	2	1	6	972	969	14,246	16,187	71,201	12,224	61,120	10,081	23,734
10/4/2019	2	2	8	910	13,773	14,060	28,743	99,944	24,448	85,568	14,376	24,986
10/5/2019	0	0	8	749			749	100,693	-	85,568	15,125	20,139
10/6/2019
10/7/2019	2	2	10		13,105	14,627	27,732	128,425	24,448	110,016	18,409	21,404
10/8/2019	2	2	12	1,039	13,375	14,505	28,919	157,344	24,448	134,464	22,880	22,478
10/9/2019	2	2	14	900	11,814	13,809	26,523	183,867	24,448	158,912	24,955	22,983
10/10/2019	2	2	16	448	11,159	13,595	25,202	209,069	24,448	183,360	25,709	23,230
10/11/2019	2	2	18	950	1,821	14,655	17,426	226,495	24,448	207,808	18,687	22,650
10/12/2019	0	0	18	927			927	227,422	-	207,808	19,614	20,675
10/13/2019
10/14/2019	2	2	20	2	13,757	14,181	27,940	255,362	24,448	232,256	23,106	21,280
10/15/2019	2	2	22	921	11,802	14,047	26,770	282,132	24,448	256,704	25,428	21,702
10/16/2019	2	2	24	934	12,560	14,446	27,940	310,072	24,448	281,152	28,920	22,148
10/17/2019	2	2	26	491	10,269	14,480	25,240	335,312	24,448	305,600	29,712	22,354
10/18/2019	2	2	28	4,284	12,141	13,738	30,163	365,475	24,448	330,048	35,427	22,842
10/19/2019	0	0	28	3,907			3,907	369,382	-	330,048	39,334	21,728
10/20/2019
10/21/2019	2	2	30		12,805	13,528	26,333	395,715	24,448	354,496	41,219	21,984
10/22/2019	2	2	32	4,146	13,131	14,352	31,629	427,344	24,448	378,944	48,400	22,492
10/23/2019	2	2	34	747	9,818	13,881	24,446	451,790	24,448	403,392	48,398	22,590
10/24/2019	2	2	36	910	12,996	658	14,564	466,354	24,448	427,840	38,514	22,207
10/25/2019	2	2	38	-	-	-	-	466,354	24,448	452,288	14,066	21,198
10/26/2019
10/27/2019
10/28/2019	2	2	40		11,259	10,659	21,918	488,272	24,448	476,736	11,536	21,229
10/29/2019	2	2	42	832	11,557	14,653	27,042	515,314	24,448	501,184	14,130	21,471
10/30/2019	2	2	44	894	13,121	11,949	25,964	541,278	24,448	525,632	15,646	21,651
10/31/2019	2	2	46		3,505	13,248	16,753	558,031	24,448	550,080	7,951	21,463
11/1/2019	2	2	2	953	9,069	13,584	23,606	23,606	24,448	24,448	(842)	7,869
11/2/2019	2	2	4	762	11,493	8,499	20,754	44,360	24,448	48,896	(4,536)	7,393
11/3/2019	0	0	4	830			830	45,190	-	48,896	(3,706)	6,456
11/4/2019	2	2	6		13,627	14,663	28,290	73,480	24,448	73,344	136	8,164
11/5/2019	2	2	8	641	11,569	14,691	26,901	100,381	24,448	97,792	2,589	8,365
11/6/2019	2	2	10	941	12,595	14,338	27,874	128,255	24,448	122,240	6,015	8,550
11/7/2019	2	2	12	923	2,782	14,249	17,954	146,209	24,448	146,688	(479)	8,123
11/8/2019	2	2	14	975	12,422	9,758	23,155	169,364	24,448	171,136	(1,772)	8,065
11/9/2019	0	0	14	434			434	169,798	-	171,136	(1,338)	7,718
11/10/2019
11/11/2019	2	2	16		9,428	13,904	23,332	193,130	24,448	195,584	(2,454)	8,047
11/12/2019	2	2	18	932	12,598	14,021	27,551	220,681	24,448	220,032	649	8,173
11/13/2019	2	2	20	946	11,998	12,965	25,909	246,590	24,448	244,480	2,110	8,220
11/14/2019	2	2	22	818	10,755	14,747	26,320	272,910	24,448	268,928	3,982	8,270
11/15/2019	2	2	24	296	12,661	10,298	23,255	296,165	24,448	293,376	2,789	8,227
11/16/2019	0	0	24	824			824	296,989	-	293,376	3,613	8,027
11/17/2019
11/18/2019
11/19/2019
11/20/2019
11/21/2019
11/22/2019
11/23/2019
11/24/2019
11/25/2019

 

[DanteAmor]

Try this.
In column A you need to put a date of the month for example 1-10-2019. In the cell format you can change to simply "Oct"

Book1
	A	B	C
1		BdMOLB	BdMoHB
2	01/10/2019	7951
3	01/11/2019	3613
4	01/12/2019
5	01/01/2020
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=IFERROR(LOOKUP(2,1/(MONTH($A2)=MONTH(BdMOLB!$A$2:$A$101)),BdMOLB!$L$2:$L$101),"Not found")

---

If the name of the sheet is equal to what you have in the column heading then you can use the following formula in cell B2:

=IFERROR(LOOKUP(2,1/(MONTH($A2)=MONTH(INDIRECT(B$1&"!$A$2:$A$101"))),INDIRECT(B$1&"!$L$2:$L$101")),"Not found")

Copy across and down

---
Check the following to copy data from your sheet and paste here.

Welcome to Version 4 of the New MrExcel Message Board!

 

[raccoon588]

I still simply get a false returned.

 

[DanteAmor]

What version of Excel do you have?

Download my test file for review.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[raccoon588]

im in Microsoft Excel 2016. It is still giving me issues. i can get October to work and then Nov fails me.

 

[DanteAmor]

Did you have problems with my file?
I don't know how you have your data, you just have to have dates on both sheets.
If you want you can upload your file to the cloud to review it.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.