Format string for LOOKUPVALUE

[Anonymous]

Hey everyone!

I am trying to use LOOKUPVALUE to get some data to another table, but I have a problem with the string. I have the following data:

Table1

Employee/Supplier	Count
Peter Parker Spiderman	1000

Table2

Employee/Supplier	No. Tickets
Parker Spiderman, Peter

I am trying to use a LOOKUPVALUE to write the value of Count from Table1 to N0. Tickets field in Table2. My problem is that these two strings, although they represent the same value (a name followed by two surnames), because of the structure the LOOKUPVALUE do not recognise it. Is there a way to use a FORMAT in the LOOKUPVALUE so I can match them?

Thanks in advance!

[AntonioM · Accepted solution]

I'm not sure you can FORMAT to rearrange the text. Could you split the columns in Power Query? You could either then recombine them into the same format or use Lookupvalue to search for first name and last name separately.

LOOKUPVALUE ( [Count], Table1[First Name], Table2[First Name], Table1[Surname], Table2[Surname] )