Help with my xlookup partial match formula
I have a list of clients from one database whose names need to be updated in another database to match, and their names are just slightly different from one another. I’ve been trying to run an xlookup partial match to search for an approximate match, but it’s not really working. Is there a formula to solve for something like this?
Example: the Company name in the database that needs to be updated is “AGK MCD#19542”, and the name it should match is “AGK MCD 19542”. Another example, the name that needs to be updated is in the system as “MCD#14114 (AGK)” and the name it should match is “AGK MCD 14114”.
My formula is as follows: =XLOOKUP(“*”&D16&”*”,’HubSpot Companies’!$B$2:$B$7320,’HubSpot Companies’!$A$2:$A$7320,”not found”,2)
So for example, the value in D16 is AGK MCD 19542, and the value within the lookup array that it should match to is AGK MCD#19542, but with the formula written as is, it’s returning ‘not found’.
The lookup array values and the lookup values do mostly have store numbers that match one another; is there a way to search only on the number and not the rest of the cell text? This is a list of over 800 companies so if we can find a formula to do it that would be amazing.
I have a list of clients from one database whose names need to be updated in another database to match, and their names are just slightly different from one another. I’ve been trying to run an xlookup partial match to search for an approximate match, but it’s not really working. Is there a formula to solve for something like this? Example: the Company name in the database that needs to be updated is “AGK MCD#19542”, and the name it should match is “AGK MCD 19542”. Another example, the name that needs to be updated is in the system as “MCD#14114 (AGK)” and the name it should match is “AGK MCD 14114”. My formula is as follows: =XLOOKUP(“*”&D16&”*”,’HubSpot Companies’!$B$2:$B$7320,’HubSpot Companies’!$A$2:$A$7320,”not found”,2) So for example, the value in D16 is AGK MCD 19542, and the value within the lookup array that it should match to is AGK MCD#19542, but with the formula written as is, it’s returning ‘not found’. The lookup array values and the lookup values do mostly have store numbers that match one another; is there a way to search only on the number and not the rest of the cell text? This is a list of over 800 companies so if we can find a formula to do it that would be amazing. Read More
