Index/match or vlookup question
Hello ,
I try to find any solution for the following problem without any success. I hope somebody can guide me how to do it.
I try to do some index match or vlookup but the tricky point that in my lookup array i have many contacts .
Smartsheet A
So I try to find within this contacts one exact match email address but from another smartsheet.
Smartsheet B (mapping)
and I would like to show results to see.
Unfortunately I can receive results only if in Smartsheet A I have one contact , if I have a multiple contacts , than the formula cant find and match.
=INDEX({Smartsheet B results to see}, MATCH(separated]@row, {find}, 0))
Separated column multiple contacts
Do you have any idea how to do it ?
Thank you ,
Br,
Tibor
Answers
-
@Tibor Are you trying to get multiple values? If so, INDEX/MATCH and VLOOKUP are only good for 1 to 1 matches and never with multiple matches as their logic will end when they find 1 instance. What you could do is COLLECT.
=COLLECT({Smartsheet B results to see}, {find}, [separated]@row)
I apologize if this is not what you are trying to do.
-
Hello ,
Thank you very much for help , in result I see #CONTACT EXPECTED for =COLLECT({Smartsheet B results to see}, {find}, [separated]@row).
Basically I would like to check multiple contacts in cell ( separated column) , and if in other smartsheet Find column one of the email address exist (in the multiple contactw) then in result I would like to show (smartsheet B result).
-
Hi @Tibor
There currently isn't a way to match individual values from a multi-select cell to individual values in another multi-select cell. The formula will look to compare the cell in its entirety to the other cell.
For example, it can find a match between:
[Cell 1] - A, B, C
[Cell 2] - A, B, C
but not
[Cell 1] - A, B, C
[Cell 2] - A, B
This is because it sees the match as "A, B, C" is not "A, B". Does that make sense?
What you can do is look for a single value within a multi select cell:
---- Does [Cell A] have "A" selected? Yes / No
Are either of your columns single select?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!