find data based on a given date and return values to a table

[bhalbach]

I have a set of data on sheet1 that I would like to look through the dates in column S and return ONLY the values to sheet2 that are >= the date I specify on sheet2 A1. There will be multiple results.

Any help would be appreciated.

 

[Toadstool]

Hi Balhalbach,

Like this?

BHalbach.xlsx
	S	T
1	Dates
2	1/1/2020
3	3/3/2020
4	4/4/2020
5	5/5/2020
6	6/6/2020
7	7/7/2020
8
Sheet1

BHalbach.xlsx
	A	B
1	4/4/2020	4/4/20
2		5/5/20
3		6/6/20
4		7/7/20
5
6
Sheet2

Cell Formulas
Range	Formula
B1:B6	B1	=IFERROR(INDEX(Sheet1!$S$2:$S$9999,AGGREGATE(15,6,ROW(Sheet1!$S$2:$S$9999)-ROW(Sheet1!$S$1)/(Sheet1!$S$2:$S$9999>=$A$1),ROW())),"")

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For example, if you have Excel 365 and want multiple columns returned you can do so with one single simple formula entered in one cell.

bhalbach.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Hdr1	Hdr2	Hdr3	Hdr4	Hdr5	Hdr6	Hdr7	Hdr8	Hdr9	Hdr10	Hdr11	Hdr12	Hdr13	Hdr14	Hdr15	Hdr16	Hdr17	Hdr18	Dates	Hdr20	Hdr21
2	90	57	38	77	77	22	19	4	37	67	26	80	13	26	33	82	49	19	1/01/2020	5	7
3	30	80	38	43	8	92	99	33	52	12	88	95	93	41	48	86	10	54	3/03/2020	89	72
4	75	74	53	65	91	8	99	55	99	79	14	71	15	84	16	44	73	69	4/04/2020	29	11
5	29	37	16	97	37	36	98	44	91	79	15	27	63	16	69	99	39	8	5/05/2020	64	53
6	9	37	2	39	82	96	52	42	59	51	54	34	15	43	65	51	24	22	6/06/2020	95	74
7	34	46	54	35	31	45	88	6	54	82	24	14	26	23	11	38	90	32	7/07/2020	12	18
Sheet1

bhalbach.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	4/04/2020
2	Hdr1	Hdr2	Hdr3	Hdr4	Hdr5	Hdr6	Hdr7	Hdr8	Hdr9	Hdr10	Hdr11	Hdr12	Hdr13	Hdr14	Hdr15	Hdr16	Hdr17	Hdr18	Dates	Hdr20	Hdr21
3	75	74	53	65	91	8	99	55	99	79	14	71	15	84	16	44	73	69	4/04/2020	29	11
4	29	37	16	97	37	36	98	44	91	79	15	27	63	16	69	99	39	8	5/05/2020	64	53
5	9	37	2	39	82	96	52	42	59	51	54	34	15	43	65	51	24	22	6/06/2020	95	74
6	34	46	54	35	31	45	88	6	54	82	24	14	26	23	11	38	90	32	7/07/2020	12	18
Sheet2

Cell Formulas
Range	Formula
A2:U6	A2	=FILTER(Sheet1!A1:U7,Sheet1!S1:S7>=A1,"")
Dynamic array formulas.

 

[bhalbach]

Peter,

If I copy and paste the formula into my spreadsheet you provided it says "That function isn't valid"

If I wanted to return only specific column data and not all the column data, would a variation of the same formula work? Say I wanted to return data in columns N, O, P, Q, R & T only if the date in column S is >= sheet2A1

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For example, if you have Excel 365 and want multiple columns returned you can do so with one single simple formula entered in one cell.

bhalbach.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Hdr1	Hdr2	Hdr3	Hdr4	Hdr5	Hdr6	Hdr7	Hdr8	Hdr9	Hdr10	Hdr11	Hdr12	Hdr13	Hdr14	Hdr15	Hdr16	Hdr17	Hdr18	Dates	Hdr20	Hdr21
2	90	57	38	77	77	22	19	4	37	67	26	80	13	26	33	82	49	19	1/01/2020	5	7
3	30	80	38	43	8	92	99	33	52	12	88	95	93	41	48	86	10	54	3/03/2020	89	72
4	75	74	53	65	91	8	99	55	99	79	14	71	15	84	16	44	73	69	4/04/2020	29	11
5	29	37	16	97	37	36	98	44	91	79	15	27	63	16	69	99	39	8	5/05/2020	64	53
6	9	37	2	39	82	96	52	42	59	51	54	34	15	43	65	51	24	22	6/06/2020	95	74
7	34	46	54	35	31	45	88	6	54	82	24	14	26	23	11	38	90	32	7/07/2020	12	18
Sheet1

bhalbach.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	4/04/2020
2	Hdr1	Hdr2	Hdr3	Hdr4	Hdr5	Hdr6	Hdr7	Hdr8	Hdr9	Hdr10	Hdr11	Hdr12	Hdr13	Hdr14	Hdr15	Hdr16	Hdr17	Hdr18	Dates	Hdr20	Hdr21
3	75	74	53	65	91	8	99	55	99	79	14	71	15	84	16	44	73	69	4/04/2020	29	11
4	29	37	16	97	37	36	98	44	91	79	15	27	63	16	69	99	39	8	5/05/2020	64	53
5	9	37	2	39	82	96	52	42	59	51	54	34	15	43	65	51	24	22	6/06/2020	95	74
6	34	46	54	35	31	45	88	6	54	82	24	14	26	23	11	38	90	32	7/07/2020	12	18
Sheet2

Cell Formulas
Range	Formula
A2:U6	A2	=FILTER(Sheet1!A1:U7,Sheet1!S1:S7>=A1,"")
Dynamic array formulas.

 

[bhalbach]

What part of the formula would I change to return values in other columns? Say I want to return values in columns N, O, P, Q, R & T?

Hi Balhalbach,

Like this?

BHalbach.xlsx
	S	T
1	Dates
2	1/1/2020
3	3/3/2020
4	4/4/2020
5	5/5/2020
6	6/6/2020
7	7/7/2020
8
Sheet1

BHalbach.xlsx
	A	B
1	4/4/2020	4/4/20
2		5/5/20
3		6/6/20
4		7/7/20
5
6
Sheet2

Cell Formulas
Range	Formula
B1:B6	B1	=IFERROR(INDEX(Sheet1!$S$2:$S$9999,AGGREGATE(15,6,ROW(Sheet1!$S$2:$S$9999)-ROW(Sheet1!$S$1)/(Sheet1!$S$2:$S$9999>=$A$1),ROW())),"")

 

[Peter_SSs]

Peter,

If I copy and paste the formula into my spreadsheet you provided it says "That function isn't valid"

Then I presume that your do not have Excel 365?

.. if you have Excel 365 and want multiple columns returned ..

It would help if we knew what version(s) you have so we could provide appropriate suggestions which is why I said ..

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[bhalbach]

I figured out how to change the formula, but the formula makes no sense to me lol. If I insert lines on sheet2 where the data is getting filtered the formula doesn't work properly, when I insert line I start to lose data one line for every line a insert.

The other question I had was can the results be put in a Table that expands and shrinks to accommodate the results?

 

[Peter_SSs]

I figured out how to change the formula,

Which formula and what did you change it to?

The other question I had was ..

The other question I had was what Excel version are you using?

The other question I had was can the results be put in a Table that expands and shrinks to accommodate the results?

Yes, once the above issues are resolved.

 

[Toadstool]

What part of the formula would I change to return values in other columns? Say I want to return values in columns N, O, P, Q, R & T?

So if the data was like this

BHalbach.xlsx
	N	O	P	Q	R	S	T
1	This is N	This is O	This is P	This is Q	This is R	Dates	This is T
2	N522	O186	P199	Q410	R896	1/1/2020	T124
3	N312	O996	P648	Q448	R464	3/3/2020	T290
4	N409	O537	P156	Q327	R504	4/4/2020	T981
5	N663	O770	P815	Q725	R886	5/5/2020	T707
6	N831	O341	P193	Q132	R175	6/6/2020	T666
7	N239	O929	P161	Q633	R114	7/7/2020	T470
8	N869	O535	P882	Q558	R227	8/8/2020	T617
Sheet1

Then you could retrieve using this:

BHalbach.xlsx
	A	B	C	D	E	F	G	H
1	4/4/2020	This is N	This is O	This is P	This is Q	This is R	Dates	This is T
2		N409	O537	P156	Q327	R504	4/4/20	T981
3		N663	O770	P815	Q725	R886	5/5/20	T707
4		N831	O341	P193	Q132	R175	6/6/20	T666
5		N239	O929	P161	Q633	R114	7/7/20	T470
6		N869	O535	P882	Q558	R227	8/8/20	T617
Sheet2

Cell Formulas
Range	Formula
B2:H6	B2	=IFERROR(INDEX(Sheet1!N$2:N$9999,AGGREGATE(15,6,ROW(Sheet1!$S$2:$S$9999)-ROW(Sheet1!$S$1)/(Sheet1!$S$2:$S$9999>=$A$1),ROW()-ROW($G$1))),"")

 

[bhalbach]

Office 2016.

I was playing with Toadstool's formula

Which formula and what did you change it to?



The other question I had was what Excel version are you using?



Yes, once the above issues are resolved.