2 Sheets data combined into output sheet with match 1 field common

Using Power Query, I merged the two tabs with an Inner Join based upon the Reference Column. Here is the Mcode for each file and then the Merge.

Tab A required Trim and Clean of Reference first

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{" reference", type text}, {"total_cost", type number}, {"total_sell", type number}, {"toms_tax", type number}, {"gross_profit", type number}, {"net_profit", type number}, {"margin", type number}, {"markup", type number}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{" reference", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{" reference", Text.Clean, type text}})
in
    #"Cleaned Text"

Tab B required Trim and Clean of Reference to Match Tab A

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"agent_name", type text}, {"amount", Int64.Type}, {"created_at", type datetime}, {"created_by_name", type any}, {"currency_code", type text}, {"department", type text}, {"exported", type logical}, {"exported_by", type any}, {"iti_start_date", type datetime}, {"itinerary_internal_ref", type any}, {"lodgement_date", type datetime}, {"lodgement_ref", type any}, {"notes", type text}, {"reference", type text}, {"iti_end_date", type datetime}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"reference", "agent_name", "amount", "created_at", "created_by_name", "currency_code", "department", "exported", "exported_by", "iti_start_date", "itinerary_internal_ref", "lodgement_date", "lodgement_ref", "notes", "iti_end_date"}),
    #"Trimmed Text" = Table.TransformColumns(#"Reordered Columns",{{"reference", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"reference", Text.Clean, type text}})
in
    #"Cleaned Text"

Once cleaned, they were merged

let
    Source = Table.NestedJoin(Table2, {"reference"}, Table1, {" reference"}, "Table1", JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(Source, "Table1", {" reference", "total_cost", "total_sell", "toms_tax", "gross_profit"}, {" reference", "total_cost", "total_sell", "toms_tax", "gross_profit"})
in
    #"Expanded Table1"

The worksheet is attached for your review. When you add new rows to each of the Tabs then click on Refresh in the Data Tab. You may be required to do that twice, once to update the query and second to download the updated information.

What does "Not Working" Mean. Not a very helpful response. It appears in my worksheet as you have requested. Look at the links in my signature for understanding Power Query and how to replicate my solution. Error messages received? Different Results? Help us to help you. Cannot see your screen and flunked Mind Reading in University.

In N3, an array formula to return a clean reference number:
=IFERROR(LOOKUP("zzzzz",INDEX(TRIM(SUBSTITUTE(A!$A$2:$A$4,CHAR(9),"")),MATCH(0,COUNTIF(N$2:N2,TRIM(SUBSTITUTE(A!$A$2:$A$4,CHAR(9),"")))+IF(IF(COUNTIF(B!$N$2:$N$5,TRIM(SUBSTITUTE(A!$A$2:$A$4,CHAR(9),"")))>0,1,0)=1,0,1),0))),"")

In A3, copied across and down:
=IFERROR(INDEX(B!A$2:A$5,MATCH(Output!$N3,B!$N$2:$N$5,0)),"")

In O3, copied down:
=IFERROR(INDEX(B!O$2:O$5,MATCH(Output!$N3,B!$N$2:$N$5,0)),"")

In P3, anther array formula, copied across and down:
=IFERROR(INDEX(A!B$2:B$4,MATCH(Output!$N3,TRIM(SUBSTITUTE(A!$A$2:$A$4,CHAR(9),"")),0)),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

It would be simpler, if a helper column on A was acceptable, as the reference code is preceeded by a tab and a space and followed by several spaces

If you are running Office 2010 or later (your profile says 2007), then Power Query has provided exactly what you asked for. Don't be afraid to try new Excel features. You may be surprised as to how easy it is to get your solution.

@farhangul

We are all volunteers. This is a world wide forum and while you may be at work, others may be sleeping or involved with other family or business matters. If your issue is of an urgent nature, you may wish to contact a local professional and pay for their services. This forum is free and you must be patient as people volunteer their time as it is available. Do not bump your thread more than once every 24 hours as it is clutter and does not do you or anyone else any benefit.

Additionally, there is a pay for service sub-forum here called Commercial Services where someone may be willing to assist for payment.