I changed George St. cell D7 from a CSE formula to a regular formula that handles both Jack and Liam. The AGGREGATE function requires Excel 2010 or later, and has similar functionality to SMALL but doesn't need the CSE.
Note that the formula returns 0 when the target cell is blank. You may suppress those zeros in columns N:Q by using a Custom number format #;-#;;@. I don't have a good tweak for your conditional formatting in column R to get the pink color, however.
I honestly cannot decide whether to be deeply impressed by your formulae and their layout or horrified at the choice of tools selected for the job!
In PowerQuery all that is required is to append the input tables and filter to give the output tables. I appreciate that you want a more dynamic behaviour and so are attracted to tables. If so, I suspect that this one workbook by itself would justify a copy of Office 365 to gain access to the new dynamic array functionality (at the moment only 'insider' versions but expected for wider release any time now).
The formula I used to combine your salesperson data was
= CHOOSE( s#,
INDEX( Jack, recordNum#, columnNum# ),
INDEX( Liam, recordNum#, columnNum# ) )
This table has named sub-ranges 'combinedBranch' and 'combinedData' giving the final formula