return value if conditions are met statement

BrigitteM

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'

Protonsponge

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.