cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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 NameNew Name
RobinRobin
RobbinRobin
Ro binRobin
AdamAdam
AddamAdam
JoeJoe
JeoJoe
CarlKarl
Karl

Karl

 

Table 2

NameLooked 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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Search one column, return next column to change data

Hi @robinvjansson

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.

28.png

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

29.png

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

View solution in original post

4 REPLIES 4
Highlighted
Community Support
Community Support

Re: Search one column, return next column to change data

Hi @robinvjansson

Try LOOKUPVALUE function

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

25.png

 

Best Regards

Maggie

Highlighted
Frequent Visitor

Re: Search one column, return next column to change data

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

Highlighted
Community Support
Community Support

Re: Search one column, return next column to change data

Hi @robinvjansson

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.

28.png

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

29.png

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

View solution in original post

Highlighted
Frequent Visitor

Re: Search one column, return next column to change data

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (905)