Need some help with syntax for if formula that also contains left statement and references other sma

SHOOD

Good afternoon!

I am trying to grab some data from one smartsheet and populate into another smartsheets based on a few filters.

Smartsheet 1

Workstream column must be Curriculum & Advising

The left most 4 characters in the Objective column must match the Objective column in Smartsheet 2

If those two conditions are met, take the Sub Area column from Smartsheet 2 and populate the Sub Workstream column in Smartsheet 1, otherwise do not populate the Sub Workstream column in Smartsheet 2.

Here is what I wrote which is not working at all :)

=IF(Workstream@row="Curriculum & Advising") and left(Objective@row, 4)={WashU_Workset Playbook Range 3}),{WashU_Workset Playbook Range 4},blank)

Thanks for your help, I really appreciate it!!!

Susan

Katy H

Susan, this may require a helper column since you are technically searching for a value in the WashU_Workset Playbook and then pulling the corresponding value from a separate column. There might be a more elegant way to pull this off but I would recommend a "helper" column in each sheet, perhaps just simply called "Workstream + Objective".

The formula in this column would combine those two values together and the formula would look like this: =Workstream@row + " - " + LEFT(Objective@row, 4)

Then to perform the desired task mentioned above you would instead use an INDEX/MATCH formula to pull this information in.

=INDEX({WashU_Workset Playbook Range 4}, MATCH([Workstream + Objective]@row, {Workstream + Objective}, 0))

I hope that makes sense!