Looking up data from a master excel spreadheet 8

[PLCdeveloper]

Using Excel 2000:
I have a "master" spreadsheet that contains Part Numbers and their associated data. The PN's are all in collumn A, and the data that corresponds with the PN's reads across (3 additional collumns of data).

I want to start a 2nd spreadsheet that allows me to type in Part Numbers in collumn A; and would like this sheet to fill in the corrresponding product data from the 1st sheet.

Example: Master sheet looks like this:

PartNumber Price Location Avail
12345 1.00 Denver Yes
12350 1.50 Dallas Yes
12360 2.00 New York Yes
12370 3.00 Portland No

On the 2nd worksheet, I would like to type in the PN in collumn A, and have the spreadsheet "grab" the corresponding data from the master spreadsheet. If I enter:
12350 then col B, C, and D should be filled in:

12350 2.00 New York No

Sorry I'm such a rookie as MSOffice but I have been working on this using Get External Data for awhile and think I may be missing the forest for the trees. Thanks in advance...

 

[Wray69]

You could use a VLOOKUP

=VLOOKUP(A1,$A$2:$D$5,2,FALSE) FOR COLUMN B
=VLOOKUP(A1,$A$2:$D$5,3,FALSE) FOR COLUMN C
=VLOOKUP(A1,$A$2:$D$5,4,FALSE) FOR COLUMN D

Regards,

Wray

 

[PLCdeveloper]

Excellent, Wray, thanks, it worked as you wrote it.

Now, can the table_array parameter be in a seperate spreadsheet? Not sure how to define that.

Thanks again...

 

[dcj2]

Ahh, VLOOKUP! I love that function.

You should make sure your lookup field (in your case, column A) is sorted in ascending order. If its not, you may not find the value you're looking for.

Also, the sample Wray gave will only work if the range (A25) is on the same worksheet as the formula. That's not always convenient, and besides, it looks bad. If you want to store your lookup tables on a seperate sheet, out of sight, you'll have to reference the sheet name in your formula. Let's say your interactive entry is on Sheet1 and the lookup table is on Sheet2. Your vlookup formulas would read:

=VLOOKUP(A1,Sheet2!$A$2:$D$5,2,FALSE) for column B, etc

But we can impriove on this, too. Instead of hardcoding the sheet name and cell array, we can use a named range.

Named ranges are easy. Just select the range A25 on Sheet2. Then go to Insert, Name, Define. Enter an appropriate Name for the range (PartNums, or something like that), then click Add, and OK. Now your formula should be changed to:

=VLOOKUP(A1,PartNums,2,FALSE)

Naming the range makes the whole thing easier to read, allows you to easily expand the list of entries without having to change every formula and makes it easier to enter the lookup reference (you have to admint, typing PartNums is alot easier than Sheet2!$A$2:$D$5).

I've done workbooks with as many as 10 different data arrays for VLOOKUP. I name each range, then hide the sheet. It can be a very powerfull tool when building interactive lookups, and if you really want to get fancy, you can nest the lookups (i.e. have vlookup retrieve one value from TableA to use as a paramter in another vlookup in from TableB). Let me know if you'd like to see an example!



------------------------
Doug J.
Winchester, VA

 

[KenWright]

The optional 4th argument of FALSE in Wray's VLOOKUP formula eliminates any need for the data to be sorted. It will also ensure that only an exact match is returned, and not just a closest match. If there are duplicate values in the data then only the first match will be returned.

Regards
Ken................

 

[PeterMoran]

Hi PLCDeveloper,

Just in case your Part Numbers are in a separate workbook you can include the workbook name, and path if necessary, into the formula in front of the rangename in square brackets, as follows:

=VLOOKUP(A1,[partnumbers.xls]Sheet1!PartNums,2,FALSE)

And I'll give a star to Doug for his great explanation, if you give one to Wray for his input.

Good Luck,

Peter Moran
Two heads are always better than one

 

[PLCdeveloper]

You guys rock. Thanks to Wray69, dcj2, KenWright, and PeterMoran for all the hep. Cheers all.

 

[deebaglee]

Hi fantastic indepth into vlookup need some help tried this

Named ranges are easy. Just select the range A1:C106 on Sheet3. Then go to Insert, Name, Define. Enter an appropriate Name for the range (PartNums, or something like that), then click Add, and OK. Now your formula should be changed to:
but ddi not get this
=VLOOKUP(A1,PartNums,2,FALSE)
any areas i need to check out?
cheers

 

[xlbo]

The formula won't change itself - when you enter the formula and select the named range, it should change the cell refs to the range name but if you already have the formula in place, I doubt it will automatically correct itself

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244