return value if conditions are met statement


Hi Smartsheet community,

I need a formula to pull values from one sheet into another if certain conditions are met in both or either sheet

sheet one has the information I would like to pull into sheet 2 the column name to pull is 'Hiring Manager(s)' if the below conditions are met

'hierarchy' on sheet 1 = 1

'position name' on sheet 1 = 'position name' on sheet 2

'offer status' on sheet 1 is not 'Offer Sent'


  • Protonspounge
    Protonspounge ✭✭✭✭✭
    edited 05/28/24

    Hello Brigitte - are you looking for something like this… I hope I have read your question correctly but let me know if I miss understood:-

    You sheet 1 may look something like this?

    and you want to collect the position name, against the hiring manager if the hierarchy is 1 and offer is anything but offer sent?

    If so this could work you: (note that your "sheet 1" would be "00_New Sheet" in my syntax below.

    =IFERROR(INDEX(COLLECT({00_New Sheet_Position Name}, {00_New Sheet _Hiring Manager}, [Hiring Manger]@row, {00_New Sheet Range_Hierarchy}, 1, {00_New Sheet_Offer Status}, <>"Offer Sent"), 1), 0)

    Let me know if I am way off and I can try again.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!