Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Search one column, return next column to change data

Hi,   I'm stuck trying to figure out how to do a thing like VLOOKUP in excel.   I have a table(table 1) with Column a being Unique names(Old name) and Column B being the new name(New Name). (Clea...
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    Hi Anonymous

    The calculation is working well for me when using the data as picture I offered before. However, I can re-produce the error by introducing a duplicate row in the New Name column.

    Let me draw your attention to the two rows circled in blue.

    You most likely have one or more rows with the same value in Name column, and different values in New Name column. Since the LOOKUPVALUE function is only expecting a single column with one unique or distinct value, the New Name column is triggering the error.

    If you confirm that this is what is happening and that it is normal for the table to have duplicate values in the New Name column, then you will need to change the formula and potentially the model to accommodate this. If the table isn't meant to hold duplicates in this column, then you can remove the duplicates from the table's data source to fix the issue.

     

    Best Regards

    Maggie

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
8 years ago

Hi Anonymous

Try LOOKUPVALUE function

Looked up value =
LOOKUPVALUE ( Table1[New Name], Table1[Old Name], [Name] )

 

Best Regards

Maggie

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi v-juanli-msft,

     

    Thank you for taking your time replying and helping me!

     

    I get the error of "A table of multiple values was supplied where a single value was expected."

     

    Because I have a database with a lot of supplier that is sometimes wrongly spelled or different but technically is the same.

    So I went trhough all and next to it created a column with the Alligned Name in excel.

    And when I replace the columns New name, Old name and name with their respective, I get this error. Do you know what it means?

     

    Again, thank you!


    Best Regards,

    Robin

    • v-juanli-msft's avatar
      v-juanli-msft
      Icon for Community Support rankCommunity Support
      8 years ago

      Hi Anonymous

      The calculation is working well for me when using the data as picture I offered before. However, I can re-produce the error by introducing a duplicate row in the New Name column.

      Let me draw your attention to the two rows circled in blue.

      You most likely have one or more rows with the same value in Name column, and different values in New Name column. Since the LOOKUPVALUE function is only expecting a single column with one unique or distinct value, the New Name column is triggering the error.

      If you confirm that this is what is happening and that it is normal for the table to have duplicate values in the New Name column, then you will need to change the formula and potentially the model to accommodate this. If the table isn't meant to hold duplicates in this column, then you can remove the duplicates from the table's data source to fix the issue.

       

      Best Regards

      Maggie

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi v-juanli-msft

         

        Thank you! I've been going through everything and can't argue with you. You are spot on.

         

        Thank you for the help, it all work now!

         

        Take care!

        Best regards,

        Robin