Xlookup with offset or minifs?

[LW2016]

Hello,

I have been struggling with this formula for two days. Below is a sample of my data. I am trying to return the # of Accts in the first non-blank cell for Dec 2019 # Accts and May 2021 # Accts where that value is not greater than Enroll Month Start.

12/1/2019​

1/1/2020​

2/1/2020​

3/1/2020​

4/1/2020​

5/1/2020​

6/1/2020​

7/1/2020​

8/1/2020​

9/1/2020​

10/1/2020​

11/1/2020​

12/1/2020​

1/1/2021​

2/1/2021​

3/1/2021​

4/1/2021​

5/1/2021​

Dealer

Enrollment Date

Enroll Month Start

Dec 2019 # Accts

Jan 2020 # Accts

Feb 2020 # Accts

Mar 2020 # Accts

Apr 2020 # Accts

May 2020 # Accts

Jun 2020 # Accts

Jul 2020 # Accts

Aug 2020 # Accts

Sep 2020 # Accts

Oct 2020 # Accts

Nov 2020 # Accts

Dec 2020 # Accts

Jan 2021 # Accts

Feb 2021 # Accts

Mar 2021 # Accts

Apr 2021 # Accts

May 2021 # Accts

99991053​

5/5/2021​

5/1/2021​

6094​

6565​

6644​

6707​

6821​

6859​

6,918

99996962​

1/1/2099​

1/1/2099​

107​

261​

324​

886​

2136​

4001​

5512​

6,495

99991060​

12/2/2020​

12/1/2020​

2292​

2236​

2201​

2169​

2157​

2168​

2160​

2158​

2,143

99990869​

11/25/2020​

11/1/2020​

1935​

1921​

1916​

1910​

1901​

1889​

1877​

1877​

1866​

1854​

1841​

1837​

1827​

1825​

1,821

99990251​

2/17/2021​

2/1/2021​

1786​

1781​

1783​

1,784

99991050​

12/6/2020​

12/1/2020​

2​

7​

7​

7​

25​

1539​

1535​

1541​

1552​

1,550

99990823​

6/9/2020​

6/1/2020​

1,410

1411​

1415​

1412​

1417​

1421​

1419​

1421​

1422​

1429​

1419​

1417​

1426​

1427​

1434​

1,424

99990603​

3/12/2020​

3/1/2020​

1123​

1115​

1124​

1125​

1125​

1122​

1118​

1125​

1116​

1116​

1112​

1113​

1118​

1112​

1,116

99995041​

9/29/2020​

9/1/2020​

742​

740​

739​

740​

737​

737​

746​

750​

752​

751​

753​

751​

745​

744

99990844​

9/9/2020​

9/1/2020​

686

697​

711​

717​

725​

736​

741​

739​

737​

745​

735​

742​

745​

744​

743​

742

99990894​

12/6/2020​

12/1/2020​

1​

1​

5​

5​

5​

6​

18​

29​

29​

34​

49​

693​

693

 

[Eric W]

Welcome to the MrExcel forum!

I had a hard time trying to understand your request , but see if this is what you want:

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1			12/1/2019	1/1/2020	2/1/2020	3/1/2020	4/1/2020	5/1/2020	6/1/2020	7/1/2020	8/1/2020	9/1/2020	10/1/2020	11/1/2020	12/1/2020	1/1/2021	2/1/2021	3/1/2021	4/1/2021	5/1/2021		First non-blank cell
2	Enrollment Date	Enroll Month Start
3	5/5/2021	5/1/2021												6094	6565	6644	6707	6821	6859	6918		6094
4	1/1/2099	1/1/2099											107	261	324	886	2136	4001	5512	6495		107
5	12/2/2020	12/1/2020										2292	2236	2201	2169	2157	2168	2160	2158	2143		2292
Sheet30

Cell Formulas
Range	Formula
W3:W5	W3	=INDEX(FILTER(D3:U3,(D3:U3>0)*(D$1:U$1<C3)),1)

If not, please put in the expected results for your formula. And if you could use the XL2BB tool to display your sheet, that would be most helpful. It would keep me from having to retype all your data. Check the link in my signature or the reply box. It's easy to download, install, and use.

 

[LW2016]

Welcome to the MrExcel forum!

I had a hard time trying to understand your request , but see if this is what you want:

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1			12/1/2019	1/1/2020	2/1/2020	3/1/2020	4/1/2020	5/1/2020	6/1/2020	7/1/2020	8/1/2020	9/1/2020	10/1/2020	11/1/2020	12/1/2020	1/1/2021	2/1/2021	3/1/2021	4/1/2021	5/1/2021		First non-blank cell
2	Enrollment Date	Enroll Month Start
3	5/5/2021	5/1/2021												6094	6565	6644	6707	6821	6859	6918		6094
4	1/1/2099	1/1/2099											107	261	324	886	2136	4001	5512	6495		107
5	12/2/2020	12/1/2020										2292	2236	2201	2169	2157	2168	2160	2158	2143		2292
Sheet30

Cell Formulas
Range	Formula
W3:W5	W3	=INDEX(FILTER(D3:U3,(D3:U3>0)*(D$1:U$1<C3)),1)

If not, please put in the expected results for your formula. And if you could use the XL2BB tool to display your sheet, that would be most helpful. It would keep me from having to retype all your data. Check the link in my signature or the reply box. It's easy to download, install, and use.

Hi, thanks for your reply. It is close, but if there is no data before the Enroll Month Start, I want to return #N/A. Here is the sheet using the XL2BB tool w/ expected results in column X.

Book2
	A	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
2	Dealer	Enrollment Date	Enroll Month Start	Dec 2019 # Accts	Jan 2020 # Accts	Feb 2020 # Accts	Mar 2020 # Accts	Apr 2020 # Accts	May 2020 # Accts	Jun 2020 # Accts	Jul 2020 # Accts	Aug 2020 # Accts	Sep 2020 # Accts	Oct 2020 # Accts	Nov 2020 # Accts	Dec 2020 # Accts	Jan 2021 # Accts	Feb 2021 # Accts	Mar 2021 # Accts	Apr 2021 # Accts	May 2021 # Accts	last month before enroll or last value in row	first value in row	Expected results: first value in row before enrollment date
3	99991053	5/5/2021	5/1/2021												6094	6565	6644	6707	6821	6859	6,918	6,859	6,094	6,094
4	99996962	1/1/2099	1/1/2099											107	261	324	886	2136	4001	5512	6,495		107	107
5	99991060	12/2/2020	12/1/2020										2292	2236	2201	2169	2157	2168	2160	2158	2,143	2,201	2,292	2,292
6	99990869	11/25/2020	11/1/2020				1935	1921	1916	1910	1901	1889	1877	1877	1866	1854	1841	1837	1827	1825	1,821	1,877	1,935	1,935
7	99990251	2/17/2021	2/1/2021															1786	1781	1783	1,784	1,786	1,786	#N/A	there is no value < Enroll Month Start
8	99991050	12/6/2020	12/1/2020									2	7	7	7	25	1539	1535	1541	1552	1,550	7	2	2
9	99990823	6/9/2020	6/1/2020			1,410	1411	1415	1412	1417	1421	1419	1421	1422	1429	1419	1417	1426	1427	1434	1,424	1,412	1,410	1,410
10	99990603	3/12/2020	3/1/2020				1123	1115	1124	1125	1125	1122	1118	1125	1116	1116	1112	1113	1118	1112	1,116	1,123	1,123	#N/A	there is no value < Enroll Month Start
11	99995041	9/29/2020	9/1/2020					742	740	739	740	737	737	746	750	752	751	753	751	745	744	737	742	742
Sheet1

 

[Peter_SSs]

Try this modification

=INDEX(FILTER(D3:U3,(D3:U3>0)*(D$1:U$1<C3),NA()),1)

 

[LW2016]

=INDEX(FILTER(D3:U3,(D3:U3>0)*(D$1:U$1<C3),NA()),1)

Perfect, thank you so much! I did not expect such quick responses - I will definitely ask for help quicker next time.

 

[Eric W]

Glad we could help! Thanks for the feedback.

Come back anytime, there's a wealth of information here, and lots of eager helpers.