Lookup fucntion

[rohitMe]

Hi all,

I want to know whether I can perform a lookup in a table in the following way:

-All the values I want to search are listed in one column(Note: I want to search multiple values using only one lookup function).

-The column in which I am looking the value are in a second table.

-The value I want to return are in the second table.

If there is a way to do this please share it with me. Please share an example with the solution. Do ask if you don't understand my Question. Also if there is an alternate way of doing this without using the lookup function please share

Thanks

[v-piga-msft]

Hi @rohitMe,

Have you solved your problems?

If you have solved, could you share your solution or always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

In addition, you could have a good look at this article which including the example of Lookup value function.

If you still need help, could you share your data sample and you desired output, so that we can help further investigate on it?

Best Regards,

Cherry

[rohitMe]

Hi @v-piga-msft and @Stachu

This is a snapshot of my data 

Table 1Table 2

As you can see there are duplicate values in my customer mobile problem. Table 1 has only those entries with status Cancelled while Table 2 has entries with Status Closed as well as Cancelled. Now my search criteria is Customer Mobile column of Table 1 only values to be searched in table 1. The return value should be the Customer Id from Table 2 to be returned in Table 1.

I was able to achieve this using the vlookup function in excel but it also returned the value of the Customer Id corresponding to the one it was searching. Is there any way to avoid it. Contact me for any further clarification.

Thanks and Regards

Rohit

[Stachu]

I would suggest creating calculated column in Table1, with following syntax

Customer Id 2 = 
VAR CurrentMobileNr = Table1[Customer Mobile]
RETURN
CALCULATE(FIRSTNONBLANK(Table2[Customer Id],TRUE()),Table2[Customer Mobile]=CurrentMobileNr)

[rohitMe]

HI @Stachu

The solution that you gave returns only one Customer Id but it should return list of all the Customer Id of the Customer Mobile which is repeated in table 2.

Your Solution does help me find out the Customer Mobile which are duplicate.

Thanks and Regards

Rohit

[Stachu · Accepted solution]

this will give all the customer Ids

Customer Id 2 =
VAR CurrentMobileNr = Table1[Customer Mobile]
RETURN
    CALCULATE (
        CONCATENATEX ( Table2, Table2[Customer Id], "," ),
        Table2[Customer Mobile] = CurrentMobileNr
    )

EDIT
no duplicates version

Customer Id 2 =
VAR CurrentMobileNr = Table1[Customer Mobile]
VAR NoDuplicates =
    FILTER (
        SUMMARIZE ( Table2, Table2[Customer Mobile], Table2[Customer Id] ),
        Table2[Customer Mobile] = CurrentMobileNr
    )
RETURN
    CONCATENATEX ( NoDuplicates, [Customer Id], "," )

[Aravindbala]

thank you

[Stachu]

can you share example of the tables? otherwise it's not specific enough
in general I'd say that FILTER should do the job, but it depends on exact structures, joins, etc.

BTW for future reference - if you post sample tables, info on joins etc. it's much easier to anwser the question