How to assign contact with VLOOKUP?

KarenTF

Hello! I've been trying to get a contact assignment to work within my sheet and I'm not having any luck. I'm trying to have a contact automatically populate based on the site selected (in the same sheet and same row) so I can run an automation to notify said contact upon submission of a form. I initially thought this would be an IF function, but after looking up other similar scenarios I found that VLOOKUP function is the correct way of doing this, but I've never used this formula so I'm having some issues resolving the error. Any suggestions?

David Ruff

Hi KarenTF,

Apologies for misunderstanding your question. If you know the contact person for each location, I would suggest setting up a separate sheet and populating it as a lookup table (database). Then you can use the VLOOKUP() or INDEX(MATCH()) function within a cross-sheet reference formula.

This article describes how to build out a cross-sheet reference, if you are unfamiliar with them: https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

To your original point about using an IF statement, this is possible, but not as robust as the cross-sheet reference described above. With a lookup table, you have one source of truth for who is assigned to each location and it can easily be updated or expanded.

Below is an example IF statement, where each location and contact is assigned within the formula. This would obviously need to be expanded/updated if more locations are added, or contacts change.

=IF(Location@row= "United States", "1@location.com", IF(Location@row = "Great Britain", "2@location@row", IF(Location@row = "Germany", "3@location.com", IF(Location@row="Japan", "4@location.com", "No Contact Exists for this location"))))

David Ruff

Hi KarenTF,

You can use VLOOKUP, but if you move columns around, it can "break" the formula. For VLOOKUP to work, the value in the table you're looking for must be the first column. For this reason, I prefer using INDEX(MATCH()), so I've provided an example for VLOOKUP and INDEX(MATCH()) below:

VLOOKUP:

=VLOOKUP([Location Selected]@row, Location:[Name Assigned], 2, 0)

INDEX(MATCH()):

=INDEX([Name Assigned]:[Name Assigned], MATCH([Location Selected]@row, Location:Location, 0))

Andrée Starå

Hi @KarenTF

I hope you're well and safe!

You could use a VLOOKUP or INDEX/MATCH structure, and I would recommend using the INDEX/MATCH option.

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

Here's an example of the structure for a cross-sheet formula structure

=INDEX({ColumnWithTheValueYouWantToShow}, MATCH(CellThatHaveTheValueToMatch@row,
{ColumnWithTheValueToMatchAgainsTheCell}, 0)

Here's an example if it's in the same sheet.

=INDEX(ColumnWithTheValueYouWantToShow:ColumnWithTheValueYouWantToShow, 
MATCH(CellThatHaveTheValueToMatch@row,
ColumnWithTheValueToMatchAgainsTheCell:ColumnWithTheValueToMatchAgainsTheCell, 0)

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

KarenTF

I'm not sure if this did answer my question...so it looks like the INDEX/MATCH functions are a good way to sort and assign to someone after the data has been populated (i.e. in a database, assigning based on what's already there). I'm currently trying to build a system (therefore, no data) that when someone fills out a form and selects their location, the corresponding person of contact for that location, is automatically added in the sheet. The end-result I'm wanting to do is to be able to automate the responses to that contact upon submission, using that contact column.

David Ruff

Hi KarenTF,

Apologies for misunderstanding your question. If you know the contact person for each location, I would suggest setting up a separate sheet and populating it as a lookup table (database). Then you can use the VLOOKUP() or INDEX(MATCH()) function within a cross-sheet reference formula.

This article describes how to build out a cross-sheet reference, if you are unfamiliar with them: https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

To your original point about using an IF statement, this is possible, but not as robust as the cross-sheet reference described above. With a lookup table, you have one source of truth for who is assigned to each location and it can easily be updated or expanded.

Below is an example IF statement, where each location and contact is assigned within the formula. This would obviously need to be expanded/updated if more locations are added, or contacts change.

=IF(Location@row= "United States", "1@location.com", IF(Location@row = "Great Britain", "2@location@row", IF(Location@row = "Germany", "3@location.com", IF(Location@row="Japan", "4@location.com", "No Contact Exists for this location"))))

KarenTF

YES!! This is exactly what I was needing. Thank you so much!

Andrée Starå

@KarenTF

Happy to help!

Another option could be to add the options in a Workflow that would change the value depending on what's selected.

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!