Help with formulas using contact list

Hello,

Although this is possible to do with a combination of an IF(INDEX(MATCH formula, there may be a simpler solution. Instead of building out multiple sheets, one for each person, have you though about creating one Report instead?

You could set up the Report criteria under "Who" to be if "Route 1" is assigned to the "Current User", OR "Route 2" is assigned to the "Current User" OR if "Route 3" is assigned to the "Current User". This way the Report will only show the rows that have the Current User assigned, even if they are in just one of the three columns. You can read more about Reports and the Who criteria in our Help Center, here.

The user would need to be shared to the underlying sheet though, so if you want to go the formula way, with multiple sheets, you could build something similar to this in your destination sheet:

=IF(INDEX({Route 1 Assigned To}, MATCH(Job@row, {Job in Source Sheet})) = "Alex", "alex@alexsemail.com", "")

This will search to see if the corresponding cell is Alex, and if it is, return his email address in that cell. If not, it will return a blank cell. You would need to update the first reference for each column (Route 2, etc), but then you could drag-fill down the whole column. The three Route columns would need to be Contact columns in your destination sheet as well.

Here are the corresponding articles for more info:

• IF function
• INDEX Function 
• MATCH Function 
• @row in formulas 

Cheers,

Genevieve