Pull Values from another workbook into Cells based on a lookup of a partial keyword

Bond00

Board Regular
Joined
Oct 11, 2017
Messages
142
Office Version
  1. 2021
  2. 2016
Platform
  1. Windows
So like the title suggests, i need to be able to pull values into cells below based on the first cells data. So for example:

I have a doc and in C3 i have Hewlett Packard, i would like to be able to search a different excel doc sheet that has a large list of names with 6 rows of data below the name and pull those into the next 6 cells below the cell i am searching based off of, leaving a cell space after country.

so like in my worksheet i have:
[TABLE="class: grid, width: 335"]
<tbody>[TR]
[TD]Company Name
[/TD]
[TD]Hewlett Packard
[/TD]
[/TR]
[TR]
[TD]Contact Name
[/TD]
[TD]name
[/TD]
[/TR]
[TR]
[TD]Address
[/TD]
[TD]etc..
[/TD]
[/TR]
[TR]
[TD]City State Zip
[/TD]
[TD]city, state zip
[/TD]
[/TR]
[TR]
[TD]Country
[/TD]
[TD]etc..
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Phone
[/TD]
[TD]number
[/TD]
[/TR]
[TR]
[TD]Fax
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Email
[/TD]
[TD]email
[/TD]
[/TR]
</tbody>[/TABLE]
This is the format of my sheet to pull the info into, the Bold starts in column B3-B11 and the info is C3-C11

Based on what i have in Cell C3 in my doc it then searches through another excel doc based on a partial word title so for example it only searches for the match word "Hewlett" once it finds that in the other doc it pulls its next 6 cell values into the next 6 cells in my main doc. the other doc is kind of being used as a lookup table database for the info.


when a company is typed into the Company Name field, i would like the other cells below it to auto-populate based on information it gets from another excel sheet with the info.
I haven't made that search file yet so it could either all be in Column A and have the other values below it or the name could be in column A and the other info be in B C D etc.. (it might be cleaner and more efficient to make that way)
[TABLE="class: grid, width: 830"]
<tbody>[TR]
[TD]Company
[/TD]
[TD]Contact Name
[/TD]
[TD]Address
[/TD]
[TD]City, State Zip
[/TD]
[TD]Country
[/TD]
[TD][/TD]
[TD]Phone
[/TD]
[TD]Fax
[/TD]
[TD]Email
[/TD]
[/TR]
</tbody>[/TABLE]
(this is the format of the sheet that will have all the rows of data to search)

So it just searches for a company match in the other sheet and pulls the company data in to those cells based on that matched search. I would like it to be able to do a partial match if possible. e.g. If I type in General it will find General Motor but if there is other companies starting with General i can just type General Mo or the whole thing if needed etc.

Is this possible to do without vba? If not, using vba will be ok.

thanks :)
 

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

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,633
Latest member
DougMo

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