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


[TABLE="width: 385"]
<tbody>[TR]
[TD="width: 82, bgcolor: transparent, align: right"]28/12/2018
[/TD]
[TD="width: 430, bgcolor: transparent"]​Majestic Princess
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]28/12/2018
[/TD]
[TD="bgcolor: transparent"]Boxing Day - 3rd Test - Australia v India (Day 3)
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]29/12/2018
[/TD]
[TD="bgcolor: transparent"]BBL - Melbourne Renegades v Sydney 6ers
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]29/12/2018
[/TD]
[TD="bgcolor: transparent"]WBBL - Melbourne Renegades v Melbourne Stars
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]29/12/2018
[/TD]
[TD="bgcolor: transparent"]Day Gallops
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]29/12/2018
[/TD]
[TD="bgcolor: transparent"]Boxing Day - 3rd Test - Australia v India (Day 4)
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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
[TABLE="width: 1258"]
<tbody>[TR]
[TD]Date from[/TD]
[TD]Date to[/TD]
[TD]Event venue / roadworks location[/TD]
[TD]Council[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD="align: right"]1/10/2018[/TD]
[TD="align: right"]5/10/2018[/TD]
[TD]MILLERS-WEST GATE OUT RAMP ON, ALTONA NORTH[/TD]
[TD]HOBSONS BAY[/TD]
[TD]Ramp Closure - West Gate Freeway Outbound Millers Road Entry Ramp[/TD]
[/TR]
[TR]
[TD="align: right"]1/10/2018[/TD]
[TD="align: right"]5/10/2018[/TD]
[TD]WEST GATE IN-WILLIAMSTOWN RAMP OF, YARRAVILLE[/TD]
[TD]HOBSONS BAY,MARIBYRNONG[/TD]
[TD]Ramp Closure - West Gate Freeway Inbound Exit Ramp to Williamstown Road[/TD]
[/TR]
[TR]
[TD="align: right"]1/10/2018[/TD]
[TD="align: right"]15/10/2018[/TD]
[TD]CHILDS ROAD , EPPING[/TD]
[TD]WHITTLESEA[/TD]
[TD]Speed reductions, shoulder closures, shuttle flow and holding traffic to facilitate service locating and proving works.[/TD]
[/TR]
[TR]
[TD="align: right"]1/10/2018[/TD]
[TD="align: right"]20/10/2018[/TD]
[TD]FLINDERS STREET , MELBOURNE[/TD]
[TD]MELBOURNE[/TD]
[TD]Service Proving (NDD Works)[/TD]
[/TR]
[TR]
[TD="align: right"]1/10/2018[/TD]
[TD="align: right"]26/10/2018[/TD]
[TD]PARK STREET , MELBOURNE[/TD]
[TD]PORT PHILLIP,MELBOURNE[/TD]
[TD]Asphalt Resheeting[/TD]
[/TR]
[TR]
[TD="align: right"]2/10/2018[/TD]
[TD="align: right"]5/10/2018[/TD]
[TD]TULLAMARINE FREEWAY , AIRPORT WEST[/TD]
[TD]MORELAND,MOONEE VALLEY,HUME[/TD]
[TD]Tulla OB closure - Melrose to Airport[/TD]
[/TR]
[TR]
[TD="align: right"]2/10/2018[/TD]
[TD="align: right"]5/10/2018[/TD]
[TD]WESTERN RING IN-TULLAMARINE OUT RAMP , TULLAMARINE[/TD]
[TD]MORELAND,HUME[/TD]
[TD]M80 AB-Tulla OB ramp closure[/TD]
[/TR]
[TR]
[TD="align: right"]2/10/2018[/TD]
[TD="align: right"]5/10/2018[/TD]
[TD]WESTERN RING OUT-TULLAMARINE OUT RAMP , TULLAMARINE[/TD]
[TD]HUME[/TD]
[TD]M80 GB-Tulla OB ramp closure Detour via Melrose Dr[/TD]
[/TR]
[TR]
[TD="align: right"]2/10/2018[/TD]
[TD="align: right"]5/10/2018[/TD]
[TD]MICKLEHAM-TULLAMARINE OUT RAMP ON, TULLAMARINE[/TD]
[TD]HUME[/TD]
[TD]Mickleham OBEN ramp closure[/TD]
[/TR]
[TR]
[TD="align: right"]2/10/2018[/TD]
[TD="align: right"]5/10/2018[/TD]
[TD]KINGS WAY , MELBOURNE[/TD]
[TD]PORT PHILLIP[/TD]
[TD]Line marking works - reflectivity testing and water blasting[/TD]
[/TR]
[TR]
[TD="align: right"]2/10/2018[/TD]
[TD="align: right"]8/10/2018[/TD]
[TD]PASCOE VALE ROAD , MOONEE PONDS[/TD]
[TD]MOONEE VALLEY[/TD]
[TD]Yarra Trams are undertaking tram track renewal works on Pascoe Vale Rd between Wilson Stand Fletcher St[/TD]
[/TR]
[TR]
[TD="align: right"]2/10/2018[/TD]
[TD="align: right"]19/10/2018[/TD]
[TD]PRINCES OUT-PALMERS RAMP OF, POINT COOK[/TD]
[TD]WYNDHAM,HOBSONS BAY[/TD]
[TD]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.[/TD]
[/TR]
[TR]
[TD="align: right"]2/10/2018[/TD]
[TD="align: right"]31/01/2019[/TD]
[TD]PLENTY ROAD , BUNDOORA[/TD]
[TD]WHITTLESEA[/TD]
[TD]Drainage Nightworks Involving Lane Closures[/TD]
[/TR]
[TR]
[TD="align: right"]3/10/2018[/TD]
[TD="align: right"]6/10/2018[/TD]
[TD]WEST GATE FREEWAY , PORT MELBOURNE[/TD]
[TD]HOBSONS BAY,MELBOURNE[/TD]
[TD]External Repairs to Steel Sections of the West Gate Bridge - Load Restrictions[/TD]
[/TR]
[TR]
[TD="align: right"]3/10/2018[/TD]
[TD="align: right"]21/10/2018[/TD]
[TD]KINGS WAY , SOUTH MELBOURNE[/TD]
[TD]PORT PHILLIP[/TD]
[TD]Installation.Swap / Out of Traffic Signal Controller[/TD]
[/TR]
[TR]
[TD="align: right"]4/10/2018[/TD]
[TD="align: right"]6/10/2018[/TD]
[TD]WEST GATE FREEWAY , PORT MELBOURNE[/TD]
[TD]HOBSONS BAY,MELBOURNE[/TD]
[TD]QA/QC - Debris Panels[/TD]
[/TR]
[TR]
[TD="align: right"]4/10/2018[/TD]
[TD="align: right"]12/10/2018[/TD]
[TD]WEST GATE OUT-CITYLINK OUT RAMP , PORT MELBOURNE[/TD]
[TD]MELBOURNE[/TD]
[TD]Pit Repair works[/TD]
[/TR]
[TR]
[TD="align: right"]4/10/2018[/TD]
[TD="align: right"]3/11/2018[/TD]
[TD]EASTERN FREEWAY , CLIFTON HILL[/TD]
[TD]BOROONDARA,YARRA[/TD]
[TD]Sign Replacement[/TD]
[/TR]
[TR]
[TD="align: right"]4/10/2018[/TD]
[TD="align: right"]6/11/2018[/TD]
[TD]MELTON HIGHWAY , TAYLORS LAKES[/TD]
[TD]BRIMBANK[/TD]
[TD]Stage 3 Lane Closure 3 - Eastbound Middle and Fast Lane Closure, Westbound Fast Lane Closure and OLB Right Turn Lane Closure[/TD]
[/TR]
[TR]
[TD="align: right"]4/10/2018[/TD]
[TD="align: right"]6/11/2018[/TD]
[TD]MELTON HIGHWAY , TAYLORS LAKES[/TD]
[TD]BRIMBANK[/TD]
[TD]Stage 3 Lane Closure 8 - Eastbound Fast Lane Closure (2 lanes Westbound & 1 Lane Eastbound Open)[/TD]
[/TR]
[TR]
[TD="align: right"]4/10/2018[/TD]
[TD="align: right"]6/11/2018[/TD]
[TD]MELTON HIGHWAY , TAYLORS LAKES[/TD]
[TD]BRIMBANK[/TD]
[TD]Stage 3 Lane Closure 6 - Eastbound Slow Lane Closure (2 lanes Westbound & 1 Lane Eastbound Open)[/TD]
[/TR]
[TR]
[TD="align: right"]5/10/2018[/TD]
[TD="align: right"]8/10/2018[/TD]
[TD]VICTORIA PARADE , FITZROY[/TD]
[TD]YARRA[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6/10/2018[/TD]
[TD="align: right"]7/10/2018[/TD]
[TD]LA TROBE STREET , MELBOURNE[/TD]
[TD]MELBOURNE[/TD]
[TD]Detour signage on Victoria St during tower crane demobilisation at 50 La Trobe Street[/TD]
[/TR]
[TR]
[TD="align: right"]6/10/2018[/TD]
[TD="align: right"]14/10/2018[/TD]
[TD]HEIDELBERG ROAD , IVANHOE[/TD]
[TD]BANYULE[/TD]
[TD]Tower Crane Removal[/TD]
[/TR]
</tbody>[/TABLE]
--------------------------------
desired output
[TABLE="width: 1799"]
<tbody>[TR]
[TD]Week commencing[/TD]
[TD]Week ending[/TD]
[TD="colspan: 12"]Road Works *input: the Event - Start date - Duration of event - Routes impacted - description of impact*[/TD]
[/TR]
[TR]
[TD]8/10/2018[/TD]
[TD]14/10/2018[/TD]
[TD]CRAIGIEBURN ROAD , CRAIGIEBURNHUMETurn Lane Construction Works_Defects Repair[/TD]
[TD]CRAIGIEBURN ROAD , CRAIGIEBURNHUMETurn Lane Construction Works_Defects Repair[/TD]
[TD]CRAIGIEBURN ROAD , CRAIGIEBURNHUMETurn Lane Construction Works_Defects Repair[/TD]
[TD]CRAIGIEBURN ROAD , CRAIGIEBURNHUMETurn Lane Construction Works_Defects Repair[/TD]
[TD]YARRA BOULEVARD , RICHMONDYARRA2018 Victorian Duathlon Series[/TD]
[TD]CRAIGIEBURN ROAD , CRAIGIEBURNHUMETurn Lane Construction Works_Defects Repair[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]15/10/2018[/TD]
[TD]21/10/2018[/TD]
[TD]Station St Fairfield near Wingrove St-Buses permitted through Contra Flow *bus delays[/TD]
[TD]BOATHOUSE DRIVE , MELBOURNEMELBOURNEBuddy Fun Run Local road closure - Boathosue Drive[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]