Need help with simple lookup formula

[smalik]

I have downloaded a list of companies from a system into Excel. The data looks like this:

There are over 10,000 rows of data. All of them have similar format 6 characters (alpha or numeric) followed by a blank space, then "-" then blank space then the company name. Company name can be anywhere from 2 characters to 150 characters long.

On a separate tab, I have a couple hundreds company codes (i.e., first six characters) in one column. I need to get the matching company name for that code in the next column. Hence, I am trying to find a formula (vlookup, xlookup, simple lookup, or any other formula) to match the code and extract the company names from the list shown above.

Here is the end result I am trying to get:

I have tried the Text to column feature of Excel and then used the vlookup formula to get the required results. It worked but I am trying to avoid extra steps and see if this can be done using a formula. I do not know VBA so do not want to go in that direction.

Any help is greatly appreciated.

Thanks in advance.

PS: I am using Office 365 if that helps.

 

[jtakw]

Hi,

If you parsed out the data from your downloaded list, there would be many options, this works as is:

Book3.xlsx
	C	D	E
1	Company Code		Company Name/Code
2	801234	ABC Company	801234 - ABC Company
3	123456	Just a company	714526 - XYZ Company
4	012546	Any company	123456 - Just a company
5	714526	XYZ Company	012546 - Any company
Sheet819

Cell Formulas
Range	Formula
D2:D5	D2	=MID(LOOKUP(2,1/FIND(C2,E$2:E$5),E$2:E$5),10,255)

 

[steve the fish]

You can use wildcards in vlookup too:

=MID(VLOOKUP(C2&"*",E:E,1,0),10,99)

 

[Alex Blakenburg]

You may want to consider either:-

1. Power Query Fuzzy Matching
2. Excel's Fuzzy Matching Add-In, link to download from microsoft site below.
   Fuzzy Lookup Add-In for Excel

 

[smalik]

Thank you both (Jtakw & Steve the fish). Both formulas worked. I just did not know the mid formula. Now I know.

 

[jtakw]

You're welcome, thanks for the feedback, glad we can help.