Search one column, return next column to change data

[Anonymous]

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!

[v-juanli-msft]

Hi @Anonymous

Try LOOKUPVALUE function

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

Best Regards

Maggie

[Anonymous]

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 · Accepted solution]

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]

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