Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

New Column Lookupvalue with a filter on another Tab

Hi together,  I have to check if  in the table "data_Current" the email is matching with an email in the table "users" and it should only match when the User in the table "users" does have an model ...
Need Help
vojtechsima's avatar
vojtechsima
Icon for Super User rankSuper User
4 years ago

Hi, Anonymous ,
I tested your sample data and you got an error in the Devices table as IMEI should be unique, however, with modifying one row, here is my working solution:

Check = 
var Model_Check = LOOKUPVALUE(Devices[Model],Devices[IMEI],Current_Data[IMEI])
var MAIL_Check = LOOKUPVALUE(Users[EMAIL],Users[EMAIL],Current_Data[Email])

RETURN IF(Model_Check="Android" || Model_Check="IOS" && NOT(ISBLANK(MAIL_Check)), "Yes", "No")

 

Please note, you only need the "Check" column, I added the two more just for you to clarify what'S going on.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi, 
    as you can see in the Solution. User test.1 & test.3 & test.100 should be Yes.
    I have updated the description with one picture, maybe this help

    • vojtechsima's avatar
      vojtechsima
      Icon for Super User rankSuper User
      4 years ago

      Hi,
      Anonymous 

      I found a few inconveniences in your sample data:
      If you wanna relate the tables using "user ID", [email protected] cannot Return True, also you have duplicity in Devices, meaning the Lookup can't tell which row should be assigned to. Also, you have a typo in [email protected], his device is "Andorid", not "Android", thus it can't return TRUE. 

      If I delete duplicity and ignore Test10, here is my solution:

      Check = 
var Model_Check = LOOKUPVALUE(Devices[Model],Devices[User-ID],Current_Data[UserID])
var UserID = RELATED(Users[USER-ID])

RETURN IF(Model_Check="Android" || Model_Check="IOS" && NOT(ISBLANK(UserID)), "Yes", "No")

       




      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        is ther another possible solution ?