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

April Barrera
April Barrera ✭✭
edited 06/18/21 in Formulas and Functions

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.


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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!