How can I pull data from one Excel worksheet to another based on matching Part Number?
Asked by
ETpro (
34605
)
January 13th, 2014
I have two spreadsheets. One has a part-number column with a bunch of alphanumeric strings that are motor replacement part numbers, plus a series of additional columns of information about each part so they can be included in an online store’s database selling these parts.
The other has just three columns, a first column with many of the same part numbers the main sheet has, and two additional columns for how many cylinders the motor the part fits has, and another for whether it is a 2 or 4 stroke motor.
How can I write a formula to match part numbers and write the cylinder and stroke numbers into columns on the end of the existing larger sheet describing the parts with columns for Part-number, Name, Description, Price, Sale-price, Ship-weight, etc?
Observing members:
0
Composing members:
0
9 Answers
The Vlookup function is what you seek.
Example:
Book1 has a list of part numbers
1
2
3
4
5
Book2 has a list of part numbers,with product name
4 tchotchke
2 gizmo
5 trinket
3 thingy
1 doodad
Here’s the formula for Book1 (cell B1) to lookup the product name from Book2
=VLOOKUP(A1,[Book2.xlsx]Sheet1!$A:$B,2,FALSE)
The part numbers do not have to be in any particular order on either sheet. (if you use that FALSE argument at the end).
VLOOKUP
= vlookup( [string, value or cell reference to find], [source range, including the columns to be “brought over”], [column number, starting from the source column, of the data to be brought over], FALSE)
If the function is ended with a “TRUE” instead of FALSE then the vlookup will return “nearest value” to the source string, in addition to what @jaytkay mentioned. (Sometimes what you want is a “best guess”, I suppose. But I never use that.)
If you have both workbooks open simultaneously, you can grab the function arguments with a “point and click” strategy, which will make the fully-qualified references to [FILE.XLS]Sheet! parameters that you’ll need.
You can also start the =VLOOKUP( function and immediately upon entering the “open parenthesis” hit Ctrl-A, and Excel will present a wizard-type dialog box that will prompt for the function arguments (and show you the selection of values that you’re picking, as you choose them). Ctrl-A is a very helpful mnemonic in Excel.
Your “table of values” source for the VLOOKUP information does have to have the vlookup values as the left-most column. There are ways around that using an INDEX function, but that’s a lot more complicated to write.
May I ask, are you actually working in two different work books or two worksheets within one work book?
If you’re talking about worksheets, rather than books, but CALLING them books then “Here’s the formula for Book1 (cell B1) to lookup the product name from Book2” doesn’t make sense. In worksheet 1, there IS no B1.
Or ya’ll may be way beyond me….
The fully-qualified reference will work in either case.
I know it would work both ways, but where is the reference to cell B1 in workbook (or worksheet) 1 coming from? There is no data in B1. Is that where you’re putting the formula?
B1 is where you put the formula in Book 1
It will display the part name it finds in Book 2.
@Dutchess_III Right now, they are entirely separate Excel files with different file names. Of course, I could easily copy the data from the separate files into different worksheets within one workbook.
Thanks to @jaytkay, @Dutchess_III and @CWOTUS for the additional detail.
10/4 @jaytkay. That’s what I was trying to confirm. Now to go play!
Answer this question 
This question is in the General Section. Responses must be helpful and on-topic.
Composing members:
0
