Index/match or vlookup question

Tibor

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

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

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

Genevieve P.

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?