Lookup using multiple numbers that are separated by special character

[emily324]

Hi all,

Is there a way I can lookup multiple values that are separated by ";" and return the values and have them be separated by ";".

I want to use 'Table 2' to return the values in the 'Names' column using the 'Numbers' column as a key. See the 'desired outcome' table below for what I am looking for. Thank you!!

Table 1

Numbers

000; 111; 333

Table 2

Numbers	Names
000	Emily
111	Chrissy
222	Kathy
333	Matt

Desired Outcome

Numbers	Names
000; 111; 333	Emily; Chrissy; Matt

Best,

Emily

[emily324]

Any way to remove duplicate names if some of the same names are returned?

[Anonymous · Accepted solution]

Hi @emily324,

You can try to use following measure formula to get the correspond name list based on current number string:

formula =
VAR currNumber =
    SELECTEDVALUE ( Table1[Numbers] )
RETURN
    CONCATENATEX (
        CALCULATETABLE (
            VALUES ( Table2[Names] ),
            FILTER (
                ALLSELECTED ( Table2 ),
                SEARCH ( Table2[Numbers], currNumber, 1, -1 ) > 0
            )
        ),
        Table2[Names],
        ","
    )

Regards,

Xiaoxin Sheng

[emily324]

Any way to remove duplicate names when the same names are returned?

[emily324]

Wow, works like a charm. Thank you so much!

[emily324]

The formatting came out weird for the tables... numbers and names are two separate columns. Thanks!