Lookup based on multiple criteria

Solution in the Power Query part of Power BI, accessible via the Query Editor.

You can paste the code below in the Advanced Editor.

From viewpoint of performance, it is best to have all required data in the same row before adding the custom column:

With input in tables Managers and Allocation, you need:

1. The Managers table merged with itself to get the previous manager/business unit on the same row as the current manager/business unit.
2. Group by State to determine if there is more than 1.
3. A merge with Allocation to get its Business Unit in case the count > 1.

let
    Source = Managers,

    // Steps to merge the table with itself to get the previous manager on the same row as the manager
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Previous",JoinKind.LeftOuter),
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"Business Unit", "Manager"}, {"Previous.Business Unit", "Previous.Manager"}),

    // Group by State to determine in there is more than 1.
    #"Grouped Rows" = Table.Group(#"Expanded Previous", {"State"}, {{"Count", each Table.RowCount(_), type number}, {"AllData", each _, type table}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Business Unit", "Manager", "Index", "Index.1", "Previous.Business Unit", "Previous.Manager"}, {"Business Unit", "Manager", "Index", "Index.1", "Previous.Business Unit", "Previous.Manager"}),

    // Merge with Allocation to get its Business Unit in case the count > 1.
    #"Merged Queries1" = Table.NestedJoin(#"Expanded AllData",{"State"},Allocation,{"State"},"Allocation",JoinKind.LeftOuter),
    #"Expanded Allocation" = Table.ExpandTableColumn(#"Merged Queries1", "Allocation", {"Business Unit"}, {"Allocation.Business Unit"}),

    // Now add the custom column
    #"Added Custom" = Table.AddColumn(#"Expanded Allocation", "Header BU", each if [Manager] = [Previous.Manager] then [Previous.Business Unit] else if [Count] > 1 then [Allocation.Business Unit] else [Business Unit]),

    // Finishing touches:
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index", "Index.1", "Previous.Business Unit", "Previous.Manager", "Allocation.Business Unit"})
in
    #"Removed Columns"

Another suggestion, that returns the results from your examples, would be: if a state has more than 1 distinct managers, it gets the business unit from sheet2, otherwise it gets the first business unit for this manager.

let
    Source = Managers,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Manager"}, {{"First BU", each List.Min([Business Unit]), type number}, {"AllData", each _, type table}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Business Unit", "State", "Index"}, {"Business Unit", "State", "Index"}),
    #"Grouped Rows1" = Table.Group(#"Expanded AllData", {"State"}, {{"AllData", each _, type table}, {"Distinct Mgrs per State", each List.Count(List.Distinct([Manager])), type text}}),
    #"Expanded AllData1" = Table.ExpandTableColumn(#"Grouped Rows1", "AllData", {"Manager", "First BU", "Business Unit", "Index"}, {"Manager", "First BU", "Business Unit", "Index"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded AllData1",{"State"},Allocation,{"State"},"Allocation",JoinKind.LeftOuter),
    #"Expanded Allocation" = Table.ExpandTableColumn(#"Merged Queries", "Allocation", {"Business Unit"}, {"Allocation.Business Unit"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Allocation", "Header BU", each if [Distinct Mgrs per State] = 1 then [First BU] else [Allocation.Business Unit] ),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Index", Order.Ascending}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"Business Unit", "Manager", "State", "Header BU"})
in
    #"Removed Other Columns"

You didn't test with my last code.

These are the results I get:

From the code in this post:

Can't spend more time on this. The problem is with column Distinct Mgrs per S-V in query MLT_V_FM_ST (2)

It is defined as text, while the content are numbers. This is no error for Power Query, but it is an error when data is loaded to Excel.

(as it is no error for Power Query, you don't get any error records in your error query).

So change the type to (whole) number and the issue is solved.