Lookup or match function with multiple criteria giving different results

Using your posted workbook, I can get you most of what you want

This regular formula begins the horizontal list of Col_A people who match the "Berth at" and "Tenure" criteria

Formula:

Please Login or Register  to view this content.

Copy that formula across through cell P18

Copy D18:P18 and
...paste into D21:P21
...paste into D23:P23
...paste into D27:P27

NOTE: That formula can only accommodate ONE of each criteria. Consequently, you'd need to split the Berth at PQA & KPT into 2 separate sections so the formulas can test each separately.

Is that something you can work with?