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

  • Eric Law
    Eric Law ✭✭✭✭✭✭

    @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.

  • Tibor
    Tibor ✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!