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

Bond00

Board Regular
Joined
Oct 11, 2017
Messages
70
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:
Company Name
Hewlett Packard
Contact Name
name
Address
etc..
City State Zip
city, state zip
Country
etc..
Phone
number
Fax
Email
email

<tbody>
</tbody>
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)
Company
Contact Name
Address
City, State Zip
Country
Phone
Fax
Email

<tbody>
</tbody>
(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 :)
 

Some videos you may like

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.

Watch MrExcel Video

Forum statistics

Threads
1,112,812
Messages
5,542,634
Members
410,562
Latest member
FrankieBue
Top