Alternative to vlookup for checking multiple columns

[mitchell36]

Hi,

I have this formula that checks a date an if the date matches on another sheet. If th date matches the informaytio is posted in hat cell to another cell on a different page. My issue is I need for the formula to look at the date matc the date and post h inormatinin a ew cel on new sheet a continue to look down each column doing the same thing.

=VLOOKUP(B9,root,2,0)
so the formula looks at B9 which is the date column it then goes to the sheet named root checks the corresponding date and then posts the information on the first sheet. I need the formula
to look at the next column in root and do the same. See example below

28/12/2018	​Majestic Princess
28/12/2018	Boxing Day - 3rd Test - Australia v India (Day 3)
29/12/2018	BBL - Melbourne Renegades v Sydney 6ers
29/12/2018	WBBL - Melbourne Renegades v Melbourne Stars
29/12/2018	Day Gallops
29/12/2018	Boxing Day - 3rd Test - Australia v India (Day 4)

<tbody>
</tbody>

 

[MagiCat]

Here's a link to a page that shows you how to use SUMPRODUCT to do this.

https://www.excel-university.com/return-the-sum-of-two-or-more-columns-with-vlookup/

In addition, you can also concatenate all of the cells together using a separator like a hyphen and then looking things up based on that one cell.

 

[mitchell36]

Cheers will do and thanks.

 

[mitchell36]

This data is from my source file. Underneath is the desired output by column. The output does not go by cell by column. The formula appears correct but does not identify that multiple events occur on the same date? formula is =VLOOKUP(B3,roadwnw,16,0) it should read 1st column for date then go to the sheet (roadwnw) match date and output information to desired output

Column A Column B

Date from	Date to	Event venue / roadworks location	Council	Description
1/10/2018	5/10/2018	MILLERS-WEST GATE OUT RAMP ON, ALTONA NORTH	HOBSONS BAY	Ramp Closure - West Gate Freeway Outbound Millers Road Entry Ramp
1/10/2018	5/10/2018	WEST GATE IN-WILLIAMSTOWN RAMP OF, YARRAVILLE	HOBSONS BAY,MARIBYRNONG	Ramp Closure - West Gate Freeway Inbound Exit Ramp to Williamstown Road
1/10/2018	15/10/2018	CHILDS ROAD , EPPING	WHITTLESEA	Speed reductions, shoulder closures, shuttle flow and holding traffic to facilitate service locating and proving works.
1/10/2018	20/10/2018	FLINDERS STREET , MELBOURNE	MELBOURNE	Service Proving (NDD Works)
1/10/2018	26/10/2018	PARK STREET , MELBOURNE	PORT PHILLIP,MELBOURNE	Asphalt Resheeting
2/10/2018	5/10/2018	TULLAMARINE FREEWAY , AIRPORT WEST	MORELAND,MOONEE VALLEY,HUME	Tulla OB closure - Melrose to Airport
2/10/2018	5/10/2018	WESTERN RING IN-TULLAMARINE OUT RAMP , TULLAMARINE	MORELAND,HUME	M80 AB-Tulla OB ramp closure
2/10/2018	5/10/2018	WESTERN RING OUT-TULLAMARINE OUT RAMP , TULLAMARINE	HUME	M80 GB-Tulla OB ramp closure Detour via Melrose Dr
2/10/2018	5/10/2018	MICKLEHAM-TULLAMARINE OUT RAMP ON, TULLAMARINE	HUME	Mickleham OBEN ramp closure
2/10/2018	5/10/2018	KINGS WAY , MELBOURNE	PORT PHILLIP	Line marking works - reflectivity testing and water blasting
2/10/2018	8/10/2018	PASCOE VALE ROAD , MOONEE PONDS	MOONEE VALLEY	Yarra Trams are undertaking tram track renewal works on Pascoe Vale Rd between Wilson Stand Fletcher St
2/10/2018	19/10/2018	PRINCES OUT-PALMERS RAMP OF, POINT COOK	WYNDHAM,HOBSONS BAY	Night closure of right lane of Princes Fwy Offramp to complete any additional works involved in barrier installation (e.g. installation of gawk screen, pinning of barriers) as contingency for works to be completed during offramp closure.
2/10/2018	31/01/2019	PLENTY ROAD , BUNDOORA	WHITTLESEA	Drainage Nightworks Involving Lane Closures
3/10/2018	6/10/2018	WEST GATE FREEWAY , PORT MELBOURNE	HOBSONS BAY,MELBOURNE	External Repairs to Steel Sections of the West Gate Bridge - Load Restrictions
3/10/2018	21/10/2018	KINGS WAY , SOUTH MELBOURNE	PORT PHILLIP	Installation.Swap / Out of Traffic Signal Controller
4/10/2018	6/10/2018	WEST GATE FREEWAY , PORT MELBOURNE	HOBSONS BAY,MELBOURNE	QA/QC - Debris Panels
4/10/2018	12/10/2018	WEST GATE OUT-CITYLINK OUT RAMP , PORT MELBOURNE	MELBOURNE	Pit Repair works
4/10/2018	3/11/2018	EASTERN FREEWAY , CLIFTON HILL	BOROONDARA,YARRA	Sign Replacement
4/10/2018	6/11/2018	MELTON HIGHWAY , TAYLORS LAKES	BRIMBANK	Stage 3 Lane Closure 3 - Eastbound Middle and Fast Lane Closure, Westbound Fast Lane Closure and OLB Right Turn Lane Closure
4/10/2018	6/11/2018	MELTON HIGHWAY , TAYLORS LAKES	BRIMBANK	Stage 3 Lane Closure 8 - Eastbound Fast Lane Closure (2 lanes Westbound & 1 Lane Eastbound Open)
4/10/2018	6/11/2018	MELTON HIGHWAY , TAYLORS LAKES	BRIMBANK	Stage 3 Lane Closure 6 - Eastbound Slow Lane Closure (2 lanes Westbound & 1 Lane Eastbound Open)
5/10/2018	8/10/2018	VICTORIA PARADE , FITZROY	YARRA
6/10/2018	7/10/2018	LA TROBE STREET , MELBOURNE	MELBOURNE	Detour signage on Victoria St during tower crane demobilisation at 50 La Trobe Street
6/10/2018	14/10/2018	HEIDELBERG ROAD , IVANHOE	BANYULE	Tower Crane Removal

<tbody>
</tbody>

--------------------------------
desired output

Week commencing

Week ending

Road Works *input: the Event - Start date - Duration of event - Routes impacted - description of impact*

8/10/2018

14/10/2018

CRAIGIEBURN ROAD , CRAIGIEBURNHUMETurn Lane Construction Works_Defects Repair

CRAIGIEBURN ROAD , CRAIGIEBURNHUMETurn Lane Construction Works_Defects Repair

CRAIGIEBURN ROAD , CRAIGIEBURNHUMETurn Lane Construction Works_Defects Repair

CRAIGIEBURN ROAD , CRAIGIEBURNHUMETurn Lane Construction Works_Defects Repair

YARRA BOULEVARD , RICHMONDYARRA2018 Victorian Duathlon Series

CRAIGIEBURN ROAD , CRAIGIEBURNHUMETurn Lane Construction Works_Defects Repair

#N/A

#N/A

#N/A

#N/A

#N/A

#N/A

15/10/2018

21/10/2018

Station St Fairfield near Wingrove St-Buses permitted through Contra Flow *bus delays

BOATHOUSE DRIVE , MELBOURNEMELBOURNEBuddy Fun Run Local road closure - Boathosue Drive

#N/A

<tbody>
</tbody>