How to return a value from a reference sheet based on two columns in target sheet
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.
Best Answer
-
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.
Answers
-
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.
Help Article Resources
Categories
Check out the Formula Handbook template!