trying to display results by date

[mugwump]

I have a workbook that looks at the data page matches the date on the event page and posts the matching date information to the event page from the data page.

(see example below) data list below example. The problem I have is that if the data in the date range is more than 2 events the data does not enter on the events page. As a newbie I have gone as far as I can go. Could some one help me please?

Formula used
=VLOOKUP(B3,root_Event_Search.xlsx!rootevent,2,0)

Event page output Week commencing	Week ending
4/02/2019	10/02/2019	​​Pacific Jewel (T) Station Pier, Port Melbourne	HAL Rd 18 - Melbourne Victory v Perth Glory AAMI Park
11/02/2019	17/02/2019	University exams Caulfield Racecourse	BBL Final - TBC v TBC MCG

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Data page information

Date	Column1
15/11/2020	ICC Men's World Twenty20 Final MCG
8/03/2020	ICC Women's World Twenty20 Final MCG
24/11/2019	International Convention of Jehovah's Witnesses Marvel Stadium
23/11/2019	International Convention of Jehovah's Witnesses Marvel Stadium
22/11/2019	International Convention of Jehovah's Witnesses Marvel Stadium
22/11/2019	Kiss - End of the Road World Tour Rod Laver Arena
21/11/2019	Kiss - End of the Road World Tour Rod Laver Arena
31/10/2019	Shawn Mendes in concert Rod Laver Arena
30/10/2019	Shawn Mendes in concert Rod Laver Arena
29/10/2019	Shawn Mendes in concert Rod Laver Arena
29/10/2019	Australian Masters Athletics National Track and Field Championships Melbourne Sports & Aquatic Centre
28/10/2019	Australian Masters Athletics National Track and Field Championships Melbourne Sports & Aquatic Centre
27/10/2019	Australian Masters Athletics National Track and Field Championships Melbourne Sports & Aquatic Centre
26/10/2019	Australian Masters Athletics National Track and Field Championships Melbourne Sports & Aquatic Centre
25/10/2019	Australian Masters Athletics National Track and Field Championships Melbourne Sports & Aquatic Centre

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[mugwump]

alternative to vlookup

I have a spreadsheet of information by date that I need a formula on another sheet to look at and if the date matches post the text in the page on the corresponding date. I have tried vlookup with success with column a and b but it will not continue onto column c,d,e etc...
any suggestions please? bear in mind I am basic at this

working formula
=VLOOKUP(B3,root_Event_Search.xlsx!rootevent,2,0)
b3 being the date (changes), rootevent being the data source....

sample of data

4/03/2019 Sheffield Shield Victoria v Tasmania Day 2 4/03/2019 ​​Pacific Jewel (T) 3/03/2019 2019 JLT Community Series - Week 1 - Richmond v Melbourne 3/03/2019 HAL Rd 21 - Melbourne City v Perth Glory 3/03/2019 Carnival Legend (T) 3/03/2019 Glenferrie Festival 2019 3/03/2019 2019 Australian International Airshow & Aerospace & Defence Expo 3/03/2019 Sheffield Shield Victoria v Tasmania Day 1 3/03/2019 Commonwealth Bank Women's ODI Series -Australia v New Zealand 3/03/2019 Mordialloc Food, Wine & Music Festival 2/03/2019 2019 JLT Community Series Week 1 - Nth Melbourne v St Kilda 2/03/2019 Ulysses Club National Rally - Grand Parade 2/03/2019 2019 NAB AFLW Round 5 - Carlton v Collingwood 2/03/2019 HAL Rd 21 - Melbourne Victory v Newcastle Jets 2/03/2019 2019 Australian International Airshow & Aerospace & Defence Expo 2/03/2019 Australian Guineas Day presented by 3AW 2/03/2019 Beer Incider 2019 2/03/2019 Mordialloc Food, Wine & Music Festival 1/03/2019 Mordialloc Food, Wine & Music Festival 1/03/2019 SANZAAR Super Rugby Union Round 3 - Melbourne Rebels v Highlanders (New Zealand) 1/03/2019 ​​Pacific Jewel (T) 1/03/2019 2019 Australian International Airshow & Aerospace & Defence Expo 1/03/2019 Night Gallops 1/03/2019 University exams 28/02/2019 2019 JLT Community Series Week 1 - Carlton v Essendon 28/02/2019 SOuth Melbourne Night Market 28/02/2019 Europa 28/02/2019 Europa (T) 28/02/2019 ​Crystal Serenity 28/02/2019 ​Pacific Eden 28/02/2019 2019 Australian International Airshow & Aerospace & Defence Expo 28/02/2019 University exams 27/02/2019 Summer Night Market 27/02/2019 Arctic Monkeys - Live 2019 27/02/2019 Europa 27/02/2019 ​​Carnival Legend (T) 27/02/2019 2019 Australian International Airshow & Aerospace & Defence Expo 27/02/2019 University exams 26/02/2019 University exams 26/02/2019 Arctic Monkeys - Live 2019 26/02/2019 Bryan Ferry in concert 26/02/2019 ​Noordam 26/02/2019 2019 Australian International Airshow & Aerospace & Defence Expo 25/02/2019 University exams 25/02/2019 ​Golden Princess (T) 25/02/2019 Caravan & Camping Super Show 2019 <colgroup><col><col></colgroup><tbody> </tbody> 4/03/2019 Sheffield Shield Victoria v Tasmania Day 2 4/03/2019 ​​Pacific Jewel (T) 3/03/2019 2019 JLT Community Series - Week 1 - Richmond v Melbourne 3/03/2019 HAL Rd 21 - Melbourne City v Perth Glory 3/03/2019 Carnival Legend (T) 3/03/2019 Glenferrie Festival 2019 3/03/2019 2019 Australian International Airshow & Aerospace & Defence Expo 3/03/2019 Sheffield Shield Victoria v Tasmania Day 1 3/03/2019 Commonwealth Bank Women's ODI Series -Australia v New Zealand 3/03/2019 Mordialloc Food, Wine & Music Festival 2/03/2019 2019 JLT Community Series Week 1 - Nth Melbourne v St Kilda 2/03/2019 Ulysses Club National Rally - Grand Parade 2/03/2019 2019 NAB AFLW Round 5 - Carlton v Collingwood 2/03/2019 HAL Rd 21 - Melbourne Victory v Newcastle Jets 2/03/2019 2019 Australian International Airshow & Aerospace & Defence Expo 2/03/2019 Australian Guineas Day presented by 3AW 2/03/2019 Beer Incider 2019 2/03/2019 Mordialloc Food, Wine & Music Festival 1/03/2019 Mordialloc Food, Wine & Music Festival 1/03/2019 SANZAAR Super Rugby Union Round 3 - Melbourne Rebels v Highlanders (New Zealand) 1/03/2019 ​​Pacific Jewel (T) 1/03/2019 2019 Australian International Airshow & Aerospace & Defence Expo 1/03/2019 Night Gallops 1/03/2019 University exams 28/02/2019 2019 JLT Community Series Week 1 - Carlton v Essendon 28/02/2019 SOuth Melbourne Night Market 28/02/2019 Europa 28/02/2019 Europa (T) 28/02/2019 ​Crystal Serenity 28/02/2019 ​Pacific Eden 28/02/2019 2019 Australian International Airshow & Aerospace & Defence Expo 28/02/2019 University exams 27/02/2019 Summer Night Market 27/02/2019 Arctic Monkeys - Live 2019 27/02/2019 Europa 27/02/2019 ​​Carnival Legend (T) 27/02/2019 2019 Australian International Airshow & Aerospace & Defence Expo 27/02/2019 University exams 26/02/2019 University exams 26/02/2019 Arctic Monkeys - Live 2019 26/02/2019 Bryan Ferry in concert 26/02/2019 ​Noordam 26/02/2019 2019 Australian International Airshow & Aerospace & Defence Expo 25/02/2019 University exams 25/02/2019 ​Golden Princess (T) 25/02/2019 Caravan & Camping Super Show 2019 <colgroup><col><col></colgroup><tbody> </tbody>

<colgroup><col><col></colgroup><tbody>
</tbody>

sample of output

week start date week end date 1st event 2nd event 3rd event 4th event

25/02/2019	3/03/2019	University exams Caulfield Racecourse	2019 JLT Community Series - Week 1 - Richmond v Melbourne	#N/A	#N/A
4/03/2019	10/03/2019	Sheffield Shield Victoria v Tasmania Day 2 MCG	2019 JLT Community Series - Week 2 - Western Bulldogs v St Kilda MARS Stadium (Ballarat)	#N/A	#N/A
11/03/2019	17/03/2019	2019 JLT Community Series - Week 2 - Collingwood v Carlton	2019 NAB AFLW - Round 7 - Collingwood v Brisbane Lions	#N/A	#N/A
18/03/2019	24/03/2019	​Queen Elizabeth (T) Station Pier, Port Melbourne	AFL Round 1 - St Kilda v Gold Coast SUNS Marvel Stadium	#N/A	#N/A

<colgroup><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[bobsan42]

Re: alternative to vlookup

First of: be careful with cell references: if you are filling the formula to the right it is a good idea to make it like this:

=VLOOKUP($B3,root_Event_Search.xlsx!rootevent,2,0)

Second: probably the cause of your problem is the area defined for rootvent - it probably only has two columns.
One way to sort the problem is to redefine the named range.
The second way to is to use shee and column names like:
=VLOOKUP($B3,[root_Event_Search.xlsx]Sheet1!$A:$F,2,0)

 

[Col Delane]

Hi Mugwump

If I understand you correctly:

1. you have a list of events by date on the Data page/sheet, sometimes with more than one event occurring on the same date (but listed separately).
2. on the Events page you wish to list all the events on the Data page that fall within selected weekly date ranges (e.g. 04/02/19 to 10/02/19)
3. VLOOKUP doesn't do a complete job because it only finds the first instance of the matching date (whichever that is?)

Questions:

1. Do you only need to list events for two weekly date ranges as shown for your Event Page Output, or is this just a sample?
2. Given the variable nature of the event data, are you open to re-arranging what your Event Page looks like?
3. Can "helper" cells/columns be added to the Data page?
4. Would you prefer a formulaic solution or are you open to using VBA if necessary?
5. What version of Excel are you using (as there is new functionality/tools in newer versions that may assist)?

 

[Fluff]

@mugwump
Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. (rule 12 here: Forum Rules).

I have merged your two threads

 

[mugwump]

Re: alternative to vlookup

thankyou for your help but unfortunately I need the formula to look further right than the first 2 colums and display the data ifrom all the columns relevant to the date.

in otherwords if in the week of 10/2/19 to17/2/2019 there were 7 events in that week it should diplay them all. the first event in column b the second event in column c 3rd event in column d etc etc

 

[mugwump]

thankyou it was a genuine error due to lag my end I posted didn't see anything so posted again. Apologies it will not hppen again.

 

[mugwump]

1. the events scroll up I need to list all events in that weekly date range
2. Happy to rearrange if the solution allows to display in seperate cells all the events for that week
3. not sure on what you mean but if it contributes to it most welcome
4.very very receptive to any solution even more so if VBA does the job
5.2016

 

[Col Delane]

There appears to be something wrong with the dates in post #2 - as there are 88 events between 25/02/19 & 03/03/19 and 4 between 04/03/19 & 10/03/19

"Helper" cells/columns are cells appended to the data table that contain a formula that returns a value that can assist in subsequent summarisations, lookups, etc.

 

[Col Delane]

Mugwump: I have a solution for you (subject to validating the data as per my post #9 ) much like your request that uses Excel [Structured] Tables (with some added "helper" columns) and formulas.

Question: What is the maximum No. of events per week that you expect? (Hopefully not more than 10 otherwise this design is probably not the best approach )