Vlookup? Index Match?

[kattyg261]

I have a register with a list of names with arrival and departure dates.

I would like to create a report on another worksheet that I enter a start and end date and it draws out all the the entries from that timeframe.

I have no idea where to begin and what formula I need to be using - lookups, index match etc or something else.

Help please.

 

[Peter_SSs]

Welcome to the MrExcel board!

It is not clear whether the entries need to

• exactly match the Start Date and End Date, or
• lie entirely within those two dates, or
• just at least partially overlap those dates.

Could you give a slightly larger set of sample data and the expected results and explain again in relation to that sample data, clarifying the above points.

Also, we cannot copy from a picture to test sample data so you would get more helpers trying if you gave us that sample data with XL2BB so that we can copy for testing.

(If you have trouble with XL2BB review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of theXL2BB Instructions page linked above.)

 

[kattyg261]

Unfortunately I cant upload the sample data. I've attached another two examples.

One worksheet contains a list of people who have different arrivals dates.

I want to create a report that I enter a start and end date and all people who are arriving on the start date, in between and the end date will populate. I intend to run that report at the beginning of eah week. I want all of the information from the master list to populate.

I hope this explains better.

 

[Armstrong_N14]

maybe this?

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Visitor ID	Visitor Fname	Visitor Lname	Arrival Date	Departure Date	Finder			Start Date	7/9/2024
2	140248	Alice	Miller	7/8/2024	8/1/2024	1			End Date	8/2/2024
3	120114	Chris	Hanson	7/11/2024	8/17/2024	1
4	124696	Mark	Krygs	7/23/2024	9/7/2024	1			Visitor ID	Visitor Fname	Visitor Lname	Arrival Date	Departure Date
5	412950	John	Brigg	8/11/2024	10/5/2024	0			140248	Alice	Miller	7/8/2024	8/1/2024
6	728223	Kyle	Watson	9/4/2024	10/5/2024	0			120114	Chris	Hanson	7/11/2024	8/17/2024
7	120479	Jenn	Simmon	9/15/2024	10/28/2024	0			124696	Mark	Krygs	7/23/2024	9/7/2024
8	138920	Robin	Smith	9/26/2024	11/20/2024	0
9	101740	Andrew	Willis	10/9/2024	12/13/2024	0
10	129270	Steve	Trevor	10/18/2024	1/8/2025	0
11	972522	Susan	Little	10/23/2024	1/11/2025	0
12	139753	Sussie	Jackson	11/18/2024	1/16/2025	0
13	388035	Mary	Chan	11/20/2024	1/16/2025	0
14	247685	Joules	Cheng	11/27/2024	1/30/2025	0
Sheet1

Cell Formulas
Range	Formula
I5:M7	I5	=FILTER($A$2:$E$14,F2:F14=1)
F2:F14	F2	=IF(OR(IF(AND(D2>=MIN($J$1:$J$2),D2<=MAX($J$1:$J$2)),1,0)=1,IF(AND(E2>=MIN($J$1:$J$2),E2<=MAX($J$1:$J$2)),1,0)=1),1,0)
Dynamic array formulas.

 

[kattyg261]

Thank you.

So I can get the finder and filter to work on the same worksheet but when I try to put the filter on a different worksheet it wont work. See example.

 

[myall_blues]

The 'include' array has to have dimensions compatible with the filter array. You’re include array goes to row 2000 but your filter array only goes to row 1571. Make it either A2:E2000, or N2:N1571.

 

[kattyg261]

All good. I have worked it out now. Thanks for your help.

 

[myall_blues]

Good news. Glad we could help.

 

[Peter_SSs]

All good.

Just checking as perhaps I have misunderstood the requirement. I have altered the data in row 14 below, but shouldn't the person from row 14 in this case be included in the final list since they are in attendance during the period of interest? If they should be included then could the alternative formula in cell I12 work for you? Note that this formula does not require the 'Finder' column.

kattyg261.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Visitor ID	Visitor Fname	Visitor Lname	Arrival Date	Departure Date	Finder			Start Date	9/07/2024
2	140248	Alice	Miller	8/07/2024	1/08/2024	1			End Date	2/08/2024
3	120114	Chris	Hanson	11/07/2024	17/08/2024	1
4	124696	Mark	Krygs	23/07/2024	7/09/2024	1			Visitor ID	Visitor Fname	Visitor Lname	Arrival Date	Departure Date
5	412950	John	Brigg	11/08/2024	5/10/2024	0			140248	Alice	Miller	8/07/2024	1/08/2024
6	728223	Kyle	Watson	4/09/2024	5/10/2024	0			120114	Chris	Hanson	11/07/2024	17/08/2024
7	120479	Jenn	Simmon	15/09/2024	28/10/2024	0			124696	Mark	Krygs	23/07/2024	7/09/2024
8	138920	Robin	Smith	26/09/2024	20/11/2024	0
9	101740	Andrew	Willis	9/10/2024	13/12/2024	0
10	129270	Steve	Trevor	18/10/2024	8/01/2025	0
11	972522	Susan	Little	23/10/2024	11/01/2025	0			Visitor ID	Visitor Fname	Visitor Lname	Arrival Date	Departure Date
12	139753	Sussie	Jackson	18/11/2024	16/01/2025	0			140248	Alice	Miller	8/07/2024	1/08/2024
13	388035	Mary	Chan	20/11/2024	16/01/2025	0			120114	Chris	Hanson	11/07/2024	17/08/2024
14	247685	Joules	Cheng	1/07/2024	5/08/2024	0			124696	Mark	Krygs	23/07/2024	7/09/2024
15									247685	Joules	Cheng	1/07/2024	5/08/2024
16
Sheet3

Cell Formulas
Range	Formula
I5:M7	I5	=FILTER($A$2:$E$14,F2:F14=1)
I12:M15	I12	=FILTER(A2:E14,(D2:D14<=J2)*(E2:E14>=J1))
F2:F14	F2	=IF(OR(IF(AND(D2>=MIN($J$1:$J$2),D2<=MAX($J$1:$J$2)),1,0)=1,IF(AND(E2>=MIN($J$1:$J$2),E2<=MAX($J$1:$J$2)),1,0)=1),1,0)
Dynamic array formulas.