VLOOKUP Search value contains multiple Contacts

Jgomez

Hello there,

I have a VLOOKUP running right now in which it uses a contact column, references another sheet, and returns the coach of the contact to the next cell. These are entered when coaches meet with their contacts. The trouble is I was just reminded that coaches sometimes meet with multiple contacts in one session.

I'm wondering if there's a way to reference just one of the names in the contact cell to pull the VLOOKUP? I was thinking INDEX but I'm bad with INDEX and maybe wrote it wrong? Is there a way to just parse out 1 contact into a helper cell? I have ideas and read a few different posts here, but I can't figure out how to make it work for me.

I attached a screenshot of what I'm looking at. In the "Coach" column is the VLOOKUP using "Technician Email List" as the search criteria to another sheet, and pulling coach name back.

DKazatsky2

Hi @Jgomez,

You might not need to use a helper column in this case. Try this.

=INDEX(COLLECT({Coach}, {Technician}, HAS([Technical Email List]@row, @cell)), 1)

{Coach} = cross-sheet reference to the coach list - {Technician} = cross-sheet reference to the technician list.

It is assumed there is a 1-to-1 relationship of technician/coach on the above referenced cross sheet.

Hope this helps,

Dave

Bassam Khalil

Hi @Jgomez,

To solve your issue in Smartsheet, especially when you're dealing with multiple contacts in one cell and you want to use a piece of that information (like one contact's name) to run a VLOOKUP (or a similar lookup function), you might need to employ a combination of functions to parse out the contact you're interested in and then perform the lookup. Since I can't see the screenshot you mentioned, I'll guide you through a general approach that you can tailor to your specific setup.

Step 1: Splitting the Contacts into a Helper Column

If your contacts are separated by a specific delimiter (like a comma, semicolon, or space), you can use a formula to extract the first contact (or any specific contact) into a helper column. Assuming your contacts are separated by commas, here's how you could extract the first contact:

1. Use the LEFT and FIND functions: If you want the first contact and your delimiter is a comma, you can use these functions to extract the name before the first comma.
2. Formula example (assuming the contacts are in column A):

=LEFT([Contacts]@row, FIND(",", [Contacts]@row) - 1)

1. This formula finds the comma position and extracts everything to the left of it. If your contacts might only have one name without a delimiter, you'll need to adjust the formula to account for that (possibly using an IF statement to check for the presence of a comma).
2. Advanced Splitting (Multiple Contacts): If you need to extract a contact other than the first, or if the number of contacts varies, you might need a more complex formula or approach, possibly involving multiple helper columns or a custom script.

Step 2: Using INDEX/MATCH Instead of VLOOKUP

Once you have the desired contact in a helper column, you can use INDEX and MATCH functions for a more flexible lookup than VLOOKUP. This is especially useful if your lookup value isn't in the first column of the reference range or if you want to avoid some limitations of VLOOKUP.

The INDEX function returns the value at a specific position in a range, and MATCH finds the position of a lookup value in a range. Together, they can be used to look up the coach for the contact.

Formula example:

=INDEX([Range of Coaches], MATCH([Helper Column]@row, [Range of Contact Emails], 0))

• [Range of Coaches] is the column range where coach names are stored.
• [Helper Column]@row is the cell in your helper column where you've extracted the contact.
• [Range of Contact Emails] is the column range in your reference sheet where the contact emails are stored.

This formula looks up the position of the contact email in the reference range and returns the coach's name from the corresponding position in the coaches' range.

Adjusting for Your Specific Needs

• Multiple Contacts in One Session: If a coach meets with multiple contacts in one session and you need to reference all of them, consider whether you need to adjust your data structure or process for recording these sessions. It might involve more complex data management strategies.
• Error Handling: Incorporate error handling with IFERROR in your formulas to manage cases where a lookup doesn't find a match.

Jgomez

Hello @Bassam Khalil

Is there any way to add a comma delimiter to those cells that have multiple contacts automatically so that I don't need to instruct the user to insert commas? I want it to be as fool proof as is possible so if the comma can be added automatically it would make less potential issues for me in the future for those folks who forget to add it. I've attached the screenshot this time as an attachment instead of a pasted image, hopefully you can see it now but they're not separated by commas unfortunately.

2024-02-14 09_04_21-image.png (696×571).png

DKazatsky2

Hi @Jgomez,

You might not need to use a helper column in this case. Try this.

=INDEX(COLLECT({Coach}, {Technician}, HAS([Technical Email List]@row, @cell)), 1)

{Coach} = cross-sheet reference to the coach list - {Technician} = cross-sheet reference to the technician list.

It is assumed there is a 1-to-1 relationship of technician/coach on the above referenced cross sheet.

Hope this helps,

Dave

Jgomez

@DKazatsky2

Thanks so much for your answer!

This actually worked really well for my situation. So I'm going to mark this as the correct answer and thanks again for that!

I do have one more question for you with this formula, is there a way to make it focus only on the first name in the cell? Or is there a way for it to pull more than 1 contact in an event where @cell in "Technician Email List" has more than one name associated to more than 1 coach?

If not or if it's too complicated that's totally fine. I just imagine this question coming up in the future and want to make sure I am able to address whether its possible when it does come up.

Thanks again for all of your help!!😁

DKazatsky2

@Jgomez,

I am not aware of a way to accomplish what you are asking in the last post. For now, I am going to say no, but I will think about it more.

Jgomez

@DKazatsky2

Thank you so much for all of your help, if you come up with something let me know. But based on my previous searches there's no way to use index or VLOOKUP to pull multiple contacts to a single cell so it's okay if nothing comes up for you.