Pull Contact Info From Another Sheet Automatically

HardWork

This way as staff changes I only need to update the market details sheet to update all my other sheets.

Paul Newcome

[Department] should be a cross sheet reference pointing back to the reference sheet to be able to create the match.

Paul Newcome

Where exactly in the tracking sheet will the contact data go, and what can we use to match on?

Basically we are going to end up using an INDEX/MATCH which is similar to the VLOOKUP but a lot better in a number of ways.

HardWork

For example with the Dallas Lab column on the tracking sheet the request comes in and associates to a (1) facility and (2) department.

Based off those two parameters go and lookup on the market sheet based and pull the appropriate POC details to auto populate at the row level all the appropriate POC: CEO, CNO, Radiology Manager, etc.

Paul Newcome

Since you are wanting to match on two separate variables, you will actually end up needing an INDEX/COLLECT along the lines of:

=INDEX(COLLECT({Lookup Sheet Email Address Column}, {Lookup Sheet Facility Column}, @cell = Facility@row, {Lookup Sheet Department Column}, @cell = Department@row), 1)

HardWork

This is what I did:

Market Details Range 1 = I referenced the whole Market Details sheet

Market Details Range 1 = I only referenced the column of the POC title

Department = this is on the tracking sheet already so I changed it to [Department]

Which part am I doing wrong that I get the UNPARSEABLE error message.

I know this is a pain. I really do appreciate your help. If I can figure this formula out it will work wonders for many of my SS's.

Paul Newcome

[Department] should be a cross sheet reference pointing back to the reference sheet to be able to create the match.