Bring in cell values from another workbook from search?

Bond00

Board Regular
Joined
Oct 11, 2017
Messages
70
Is it possible to import a cell value or range of cells from 1 workbook to another based on a value?

Example: I want to be able to put a value in A1 in workbook1 and based on that value in A1 it searches for that match value in column A in workbook2. Once it finds that match in workbook2 it then brings the values in from B-F of that row # that had the match in column A in workdbook2 and outputs it in workbook1.

I hope this is possible with a function or VBA?

let me know, thanks!!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Try something like this...
=VLOOKUP($A1,'[file-name.xlsx]sheet-name'!$B$16:$E$32,COLUMNS($A$1:A1),0)
copied across
 
Upvote 0
Try something like this...
=VLOOKUP($A1,'[file-name.xlsx]sheet-name'!$B$16:$E$32,COLUMNS($A$1:A1),0)
copied across

So how would that look formula wise?
lets say i have data in A1 on workbook1 and it searches for that in another workbook, i then want the B-F values in that row# to be auto entered in A2 A3 A4 A5 and A6 in workbook1.
what is the formula i would need in each of those cells in workbook1?

sorry i know little to nothing about the vlookup function. whats the difference between that and normal lookup?
 
Upvote 0
Also is it possible to do a search on a partial word match? this is what i came up with so far.
=VLOOKUP(LEFT( $A1, FIND( " ", $A1 ) - 1 ),'[Customer List.xlsx]Sheet1'!$A$1:$H$37,COLUMNS($A$1:A1),0)

that somewhat works but not what i need. that takes the first word i type and searches based on that. i more so need whatever i type to be searched on partial matches too.
So lets say A1 on workbook2 has "Hey there"
I would like to be able to type Hey or hey th
and have it find "Hey there" in A1 and then also bring in B-F
 
Upvote 0
So how would that look formula wise?
lets say i have data in A1 on workbook1 and it searches for that in another workbook, i then want the B-F values in that row# to be auto entered in A2 A3 A4 A5 and A6 in workbook1.
what is the formula i would need in each of those cells in workbook1?

sorry i know little to nothing about the vlookup function. whats the difference between that and normal lookup?

Pretty much as I suggested, with just the ranges in the other file changing - the bolded part (I adjusted my 1st range from B$16:$E$32 to B$16:$f32 because you said your range goes across to F)
=VLOOKUP($A1,'[file-name.xlsx]sheet-name'!$B$16:$f32,COLUMNS($A$1:A1),0).
You would then just copy across as needed
 
Upvote 0
Also is it possible to do a search on a partial word match? this is what i came up with so far.
=VLOOKUP(LEFT( $A1, FIND( " ", $A1 ) - 1 ),'[Customer List.xlsx]Sheet1'!$A$1:$H$37,COLUMNS($A$1:A1),0)

that somewhat works but not what i need. that takes the first word i type and searches based on that. i more so need whatever i type to be searched on partial matches too.
So lets say A1 on workbook2 has "Hey there"
I would like to be able to type Hey or hey th
and have it find "Hey there" in A1 and then also bring in B-F

Keep in mind that the shorter your partial search string is, the greater the chance becomes of returning inaccurate results. But to do a partial search based on anything you type in A1...
=VLOOKUP($A1&"*",'[file-name.xlsx]sheet-name'!$B$16:$f32,COLUMNS($A$1:A1),0)

If A1 = Rod
my suggestion will find the 1 match it gets to. So if you wanted Rodney, it would find that IF Rodney was the 1st match it came to. However, if and a name before that was Roderic, followed by Rodney, it would return that instead.
Rodn would now find Rodney 1st, unless Rodnic came before.
Bel would find Belgium before it found Belatrix, if Belgium came 1st
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

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