If Statement for Contact in another list and a flag is raised


Hello Again,

I am trying to have a value populate (Which is a contact) if another contact is part of a list and a flag is raised in that same row.

Some background if that wasn't clear, I have two sheets, one where employees submit if they are out of office, it has their name and the person to cover, as well as the date they will be out and another flag column that raises if today is within the date range signalling they are currently OOO.

On a separate sheet issue come in via form for each employee, I want to create a formula that says if a new submission comes in with an employee that is currently out of office (based on the other sheet), then fill in a cell with the contact that is covering while they are out.

I thought I was on to something but keep getting errors, another wrinkle is in the Out of Office sheet there may be the same employee name from previous submissions but only one will be current with a flag raised.

=IF(AND(MATCH([Account Manager Name]1, {Out of Office Range 2}), {Out of Office Range 3} = 1), INDEX({Out of Office Range 5}, MATCH([Account Manager Name]1, {Out of Office Range 2}, 0)), 0)

Thanks for the help

Best Answer


  • Joseph Noel

    Thank you for your response, I see how that could work. I ended up making a new column to create a single value to search for in the Person was in the office or not, =[Account Manager]+[Out of Office], this gave me a single value JoeBlowtrue or JoeBlowfalse. So then I did a Vlookup to search for [Account Manager]@cell + true and that is returning the Out of Office contact only if the person is out of office.

    Thanks again for the help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!