getting info from 2nd spreedsheet

R

rbell

H First I'm new to Excell. What I want to do is retreive info from a 2nd
worksheet. I have the first which is a list of part numbers and Qty's
(my inventory) and a 2nd from the manufacture which has prices,
descriptions etc.
I would like to look at a PN on worksheet 1 and look it up on worksheet
2, find the price, and enter it in a colume (cell) on worksheet 1.
 
M

Max

Assume you have

In Sheet1 (your inventory)
-----
In cols A to C, data from row2 down

PN Qty Price
1111 100 ?
1112 200 ?
etc

In Sheet2 (from manufacturer)
------
In cols A to C, data from row2 down

PN Price
1111 100
1112 200

In Sheet1,
Put in C2: =INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0))
Format C2 as currency if desired, then copy down

Col C will return the prices for the PN's from Sheet2
 
R

rbell

Hi Max First thanks for the suggestion. The sheets I have are laid ou
just like your example, but when I enter it I get a #na returned.
entered the formula in sheet1 c2 and copied it down. Any othe
thoughts. Thanks Dic
 
M

Max

One possibility for the non-match is that the lookup PN values are text
numbers, while those in Sheet2's col A are real numbers. In which case, try
instead in Sheet1's C2: =INDEX(Sheet2!B:B,MATCH(A2+0,Sheet2!A:A,0))
The "+0" operation will coerce the text PN numbers to real numbers for
proper matching.

Another possibility is the other way around, i.e. PNs in Sheet2's col A are
text numbers, while the lookup values in Sheet1's col A are real numbers.
Try this to convert Sheet2's col A to real numbers. Select an empty cell and
copy it. Then select / right-click on Sheet2's col A > Paste Special >
Check "Add" > OK.

Try the above, let us know how it goes.
 
R

rbell

Hi Max You hit the nail on the head. The Mfg. Excell spread sheet was
all in text. I had put it on a machine with Excell 2003 (was using
Excell97)and it showed a box that pointed out they were text numbers.
It had a selection to convert them to number numbers. Also the Mfg
sheet has cost and retail prices that had to be converted so they could
be merged to my inventory. Mine has about 800 parts and the Mfg sheet
has about 28,000 numbers, everything they have. So once this was done
it was very easy to get what I want. Thanks again Max
BTW your initial formula worked after the conversion.
 
Top