Consolidation question

[Bungle20120]

How do I consolidate and analyse data from five sheets, all with the heading project but with some entries missing on some sheets? The spreadsheet has 215 rows in total but when I consolidate with missing information I'm only presented with 206 rows from sheet 2, 210 from sheet 3 etc.... Is there a way around that without manually entering the missing information? Thanks

[#2]

Okay, so to clarify, sheet one has the headings project, start date, end date (215 lines); sheet two has the headings project and project manager (206 lines - so some project number are missing); sheet three has the headings project and budget (206 lines); and sheet four has the headings project and funding source. I would like one consolidated spreadsheet with six columns (project, start date, end date, project manager, budget and funding source but some project numbers are missing. When this happens I'd like the missing cell to be left empty rather than the project lines becoming uneven/mixed up. Is this possible?

[#3]

@Bungle20120 

There are two obvious routes.  The first is to use Power Query and Join the Tables by the Project.  This will refresh on demand.  The other option is to use a lookup formula 

= XLOOKUP(Table1[Project],Table2[Project],Table2[Manager],"")

This calculates immediately any change is made to one of the input Tables.

[#4]

et
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Start", type date}, {"End", type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Project"}, Table2, {"Project"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Manager"}, {"Table2.Manager"})
in
    #"Expanded Table2"

@Peter Bartholomew 

[#5]

Thanks Peter