Xlookup for first word in cell?

[haskenazi]

I have company names in a cell and sometimes the company names include more than one word
I have another table that has a list of company names with a company ID in the adjacent column

I wrote an xlookup function to lookup the company name from one table in another, and return the matching company ID

However, this only works if the match is exact. If, for example the lookup value is "Best Management" and the name in the lookup array is "Best", the function will not return a value.
I thought I can combine Xlookup with Left+Find but I get an error when trying that. Im pretty sure Im on the right track because when I use the Left+Find function by itself, it returns the first word of the cell. But that's not optimal bc then I need an extra column to do the xlookup in.

I hope this is clear and appreciate any help accomplishing this task.

[way2020]

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[ChemistB]

Hard to say without a workbook but something like

Please Login or Register  to view this content.