Need help with simple lookup formula

smalik

Board Regular
Joined
Oct 26, 2006
Messages
134
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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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)
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,601
Office Version
  1. 365
Platform
  1. Windows
You can use wildcards in vlookup too:

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

smalik

Board Regular
Joined
Oct 26, 2006
Messages
134
Office Version
  1. 365
Platform
  1. Windows
Thank you both (Jtakw & Steve the fish). Both formulas worked. I just did not know the mid formula. Now I know.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, thanks for the feedback, glad we can help.
 

Forum statistics

Threads
1,141,815
Messages
5,708,740
Members
421,588
Latest member
Wawie

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
Top