Need help with simple lookup formula

smalik

Board Regular
Joined
Oct 26, 2006
Messages
186
Office Version
  1. 365
Platform
  1. Windows
I have downloaded a list of companies from a system into Excel. The data looks like this:
1614812171218.png

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:
1614815011081.png


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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Hi,

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

Book3.xlsx
CDE
1Company CodeCompany Name/Code
2801234ABC Company801234 - ABC Company
3123456Just a company714526 - XYZ Company
4012546Any company123456 - Just a company
5714526XYZ Company012546 - Any company
Sheet819
Cell Formulas
RangeFormula
D2:D5D2=MID(LOOKUP(2,1/FIND(C2,E$2:E$5),E$2:E$5),10,255)
 
Upvote 0
You can use wildcards in vlookup too:

=MID(VLOOKUP(C2&"*",E:E,1,0),10,99)
 
Upvote 0
Solution
Thank you both (Jtakw & Steve the fish). Both formulas worked. I just did not know the mid formula. Now I know.
 
Upvote 0
You're welcome, thanks for the feedback, glad we can help.
 
Upvote 0

Forum statistics

Threads
1,223,958
Messages
6,175,632
Members
452,661
Latest member
Nonhle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top