Forum Discussion
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). (Cleanup of data).
And in another table (table 2) I have a lot of names which all should be found in Column A(Name). But because table two could have the same name but with an extra space or misspelled I have looked through all names in Column A and created a Clean universal name in Column B(Looked up Name).
How can I look for the "Name" in Table 2 to find it in Table 1 under "Old Name" and replace with "New Name" into the column "Looked up name"?
Table 1
| Old Name | New Name |
| Robin | Robin |
| Robbin | Robin |
| Ro bin | Robin |
| Adam | Adam |
| Addam | Adam |
| Joe | Joe |
| Jeo | Joe |
| Carl | Karl |
| Karl | Karl |
Table 2
| Name | Looked up Name |
| Robin | |
| Robin | |
| Robbin | |
| Ro bin | |
| Adam | |
| Addam | |
| Joe | |
| Jeo | |
| Carl | |
| Karl | |
| Robin | |
| Robbin | |
| Ro bin | |
| Adam | |
| Addam | |
| Joe | |
| Jeo | |
| Carl | |
| Karl | |
| Robin | |
| Robbin | |
| Ro bin | |
| Adam | |
| Addam | |
| Joe | |
| Jeo | |
| Carl | |
| Karl | |
| … |
Many thanks!
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
4 Replies
Hi Anonymous
Try LOOKUPVALUE function
Looked up value = LOOKUPVALUE ( Table1[New Name], Table1[Old Name], [Name] )
Best Regards
Maggie
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
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