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

Joseph Noel

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

Paul Newcome

First you would want to pull the value of the checkbox as to whether or not they are out.

=INDEX({Other Sheet Checkbox Column}, MATCH([Account Manager Name]@row, {Other Sheet Name Column}, 0))

Then you say that if that box is checked

=IF(above = 1,

=IF(INDEX({Other Sheet Checkbox Column}, MATCH([Account Manager Name]@row, {Other Sheet Name Column}, 0)) = 1,

then you want to pull the associated name from the back-up column

INDEX(COLLECT({Other Sheet Back-Up Column}, {Other Sheet Name Column}, [Account Manager Name]@row, {Other Sheet Checkbox Column}, 1), 1)

and drop that in as the output for the IF statement.

=IF(INDEX({Other Sheet Checkbox Column}, MATCH([Account Manager Name]@row, {Other Sheet Name Column}, 0)) = 1, INDEX(COLLECT({Other Sheet Back-Up Column}, {Other Sheet Name Column}, [Account Manager Name]@row, {Other Sheet Checkbox Column}, 1), 1))

Paul Newcome

First you would want to pull the value of the checkbox as to whether or not they are out.

=INDEX({Other Sheet Checkbox Column}, MATCH([Account Manager Name]@row, {Other Sheet Name Column}, 0))

Then you say that if that box is checked

=IF(above = 1,

=IF(INDEX({Other Sheet Checkbox Column}, MATCH([Account Manager Name]@row, {Other Sheet Name Column}, 0)) = 1,

then you want to pull the associated name from the back-up column

INDEX(COLLECT({Other Sheet Back-Up Column}, {Other Sheet Name Column}, [Account Manager Name]@row, {Other Sheet Checkbox Column}, 1), 1)

and drop that in as the output for the IF statement.

=IF(INDEX({Other Sheet Checkbox Column}, MATCH([Account Manager Name]@row, {Other Sheet Name Column}, 0)) = 1, INDEX(COLLECT({Other Sheet Back-Up Column}, {Other Sheet Name Column}, [Account Manager Name]@row, {Other Sheet Checkbox Column}, 1), 1))

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.

Paul Newcome

Happy to help! 👍️