Look up two references over multiple pages

[QSGuy]

NB I do not want to use a table or VBA...

I use Office 2016

I have a Communications Record Sheet – where I log emails and telecoms between a client company and the company I am working for.

the layout on the attached spreadsheet “Entry Sheet” in the attached workbook consists of a three a three digit customer reference in cell B2 and the two initials of the contact added to the front of this customer reference make the individual contact’s reference in B3 and B4. The formula in G3 and G4 refers first to the sheet “Staff” and then to the sheet “Customer List”.
The first part of the formula in G3 and G4 first check if the last three letters (the company reference is ABC (the company I work for) and if not ABC, looks in the Customer List. By a slight tweak of the formula cell references, the formula in G3 and G4 ensures that I don’t have to change anything whether it is me sending to a customer or the other way around ie “to” and “from” as illustrated in rows 6 to 8.

The issue:
All that is fine, but I would like to expand the records for Consultants and for Subs and Supps (Subcontractors and Suppliers) and Others (generally “Others” is Local Authority contacts and the like (regulatory figures). In which case my formula that checks if this is an email relates to myself before looking in the Customer Sheet is no good – I need a formula that can look up multiple pages.
Secondly, I also often encounter a situation where I will have consultants sending emails to the client or to subcontractors and suppliers (and vica versa) that I am copied in on. I would like to record these as between the parties to the original email, rather than as a (copied) email to myself. In which case my formula is no good as it can only check one client company and that it checks if this is an email to myself and then looks in Customer, again, is no good.
So I now have two company names to look up and two people over multiple worksheets – that have to be interchangeable for “to” and “from”.
My full database for the different groups (clients, staff, consultants, subs and supps and others) are all laid out the same way – each organization on a new line, with the person code, name, phone numbers etc etc in the columns following the name. As this is not just a contacts spreadsheet (I have lots of other details – such as insurances, order details, payment details, company registration numbers, tax detials etc etc on each page, the data for each company runs to about 150 columns – which includes a lot more staff members: but each sheet has exactly the same columns in exactly the same places. I use different columns for different organisations – but have all columns in all sheets and just add data to the ones I need for the particular organization. The Spreadsheet is huge, so I have had to give a sample only of the data.
I use the company name as a starting point (as in a VLOOKUP) formula, ie look up the company name to get the correct row, but this is only because I could not think of a better way of doing it – ideally the formula would just look up the person reference.

My attempt:
I created a defined Name for the names of the worksheets in the “People” worksheet and tried to use the defined name “People” in an indirect formula as shown in D13 and D14 of “Entry Sheet” I was then going to use an Index-Match type formula like the one in cells G3 and G4 in that worksheet to pick the column reference for VLOOKUP.
But for some reason, I cannot even get the formula in D13 and D14 to work – despite this being a formula I have used with defined names for worksheets many times: I don’t know if I have done something different than I usually do to set up the Defined Name “People” or have not done something I usually do when I add it to the formula in D13 and D14 – but this should refurn the names as found in G3 and G4

Anyone any ideas?