Look up two references over multiple pages

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?

Why not a 'Table' and 'VBA' driven properly - a simple task.
Although following your path with a little more effort it could become almost impossible for mere mortals
torachan.

I don't like using tables as I always find it difficult to figure out where the info comes from in a table: and the spreadsheets with the data in (Customers, Subs and Supps etc) are existing spreadsheets that I refer back to in lots of other spreadsheets. I will be inputting the data (the "to" and "from") from another sheet that also has lots of other record data in it too. The printable "form" I will design for the individual data I am creating here (that I use as back-up for both creating and defending additional cost claims) is the only stand-alone part of the process.

Torahan

I have explained Tables. I don't like VBA as it tends to slow down spreadsheets too much and they seem to be a lot more unstable. The one time I did use VBA it kept crashing the spreadsheet and I had to abandon it in the end. Saving was a nightmare - and autosave really slowed down my work. I try and keep all the data for a particular contract in one workbook - so a lot of my spreadsheets are very large.

Plus a lot of my clients won't allow VBA workbooks.

Ali

You could be right about this being a knowledge thing. One of my subcontractors used to send his application for payment in in a table - a thousand line sheet with about 100 columns: my first (and last to date) experience of tables. It put me off for life!

Obviously had a bad experience, which is a pity as you are depriving yourself of structured data handling.
torachan.

It wasn't just that one experience Torachan - I have tried to create tables before, and just ended up with gobble-de-gook - there is just something about tables I cannot get my head around.

I have been coming here for about 18 months and in that time I have learnt a great deal from the good people here. When someone gives me a solution to a problem I have come across, I might spend hours dissecting the formula solution they have come up - splitting the solution down so that I understand it completely - to resolve in my own mind how they got that solution - and I usually do "get it". I have learnt a great deal this way....

But tables for some reason, I just cannot get my head around, no matter how hard I try!

Ali

I have been very busy over the last few months as I have a major project (new business) going on - so I am putting a lot of time into that whilst we have been in lockdown. I see the stuff I do with excel as a bit of relief from that - a change of scene. Every now and then I pick a spreadsheet I need a solution for and work on it. I have learnt a lot whilst I have been here as I said in my last post - I can do things now I could not even have dreamt of before I joined!

But my head can only take so much!

I have found out why the formula I use regularly was not working - I had Subs and Supps in the Named Range but the worksheet is called Sub and Supps - an extra "s" on the end of sub was obviously creating the problem as Vlookup the formula works now to find the result if I put in a column number

So I get the row number the info is on, but transferring the formula I am currently using for the column to an array formula for the column number is proving tricky as I am not good enough yet with the syntax on INDIRECT for named ranges.

I think, once I get it to work for one criteria, I can just use IF/THEN to say if L7 is not found, look for L8 - its clunky, but should work

This is the formula I am struggling to convert to a Named Range formula:

MAX(INDEX(COLUMN('Customer List'!$E$5:$CX$9)*('Customer List'!$B$5:$B$7=$L$5)*('Customer List'!$E$5:$CX$7=$L$8),))

Which gives the column number in the worksheet based on just looking in the Customer List worksheet

Revised spreadsheet