How to return a value from a reference sheet based on two columns in target sheet

April Barrera

I am trying to use two columns in a target sheet to return a value from a reference sheet.

My formula is =JOIN(COLLECT({Designated Recipient Focus Area}, {Designated Recipient Focus Area 1}, @cell = [Focus Area]@row, {Designated Recipient Service Line}, @cell = [Service Line]@row))

For the first Designated Recipient Focus Area I highlighted the who 3 columns in the PM List Sheet. For the Second Designated Recipient Focus Area 1, I only highlighted the Focus Area Column. For the Designated Recipient Service Line I only highlighted the Service Line Column.

I am trying to get the assigned project manager to populated in the field based off two criteria - the focus area and service line.

Example

Main Sheet (Target Sheet)

Focus Area /Service Line /PM

Area 1 /Administration /(where formula is to add PM name)

PM List Sheet (Reference Sheet)

Focus Area /Service Line /PM

Area 1 /Administration /April B.

Area 2/ Accounting /Jenny C.

Please let me know what is wrong with my formula.

April Barrera

This was answered under a different post for me by @Paul Newcome

He stated that I needed to have the reference had to have the same amount of columns select for each reference. That fixed the issue.

April Barrera

This was answered under a different post for me by @Paul Newcome

He stated that I needed to have the reference had to have the same amount of columns select for each reference. That fixed the issue.

Paul Newcome

Happy to help. 👍️