Help with formulas using contact list

noclue

I am trying to create an "Assigned To" sheet for each employee. I would like to pull from a contact list column on another page. I want it to return "Alex" if Alex is listed in the column and reference "Job"@row, if column has other names or blank, I would like it to return blank. Is this possible?

Ex:

On empty sheet (Assigned To), 

I need Job 1505 to show "blank" and 1506 to show "Alex" in Route 1 Assigned To column

and pull the info from the full sheet (Job List)

I would like to use MATCH([Job]@row so I can copy and paste all the way down the column. 

Genevieve P.

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