LOOKUP finding values it should not
 

I have a summary sheet for all styles of our product sold
to one company, and the actual monthly sales for each
product on separate monthly worksheets (gotten from
Access). The monthlies only have the products that actually
sold, not the full list of products listed on the summary page.
So I wrote a LOOKUP for the summary page to go to the
monthly, look for a match on the part number, and return
the quantity sold.
First one correctly returned a #N/A - good. I autofilled
the rest down. And got a #N/A for the second one, and then
the quantity of 1 for absolutely everything else. Only 6
products sold, though they all had a quantity of 1.

The formula
=LOOKUP($A4,'Jul 03'!$B$2:$B$50,'Jul 03'!$A$2:$A$50)
looks correct to me. I used 50 in my formula even though
there's only 7 rows in this particular sheet, so I could
copy it to all other columns without adjusting more than
the sheet name.
=LOOKUP($A6,'Jul 03'!$B$2:$B$50,'Jul 03'!$A$2:$A$50)
is where I start getting a return value of 1 for everything.

I cannot see the problem - help help!

LOOKUP finding values it should not
 

Hi
try
=INDEX('Jul 03'!$A$2:$A$50,MATCH($A4,'Jul 03'!
$B$2:$B$50,0))

-----Original Message-----
I have a summary sheet for all styles of our product sold
to one company, and the actual monthly sales for each
product on separate monthly worksheets (gotten from
Access). The monthlies only have the products that
actually
sold, not the full list of products listed on the summary
page.
So I wrote a LOOKUP for the summary page to go to the
monthly, look for a match on the part number, and return
the quantity sold.
First one correctly returned a #N/A - good. I autofilled
the rest down. And got a #N/A for the second one, and then
the quantity of 1 for absolutely everything else. Only 6
products sold, though they all had a quantity of 1.

The formula
=LOOKUP($A4,'Jul 03'!$B$2:$B$50,'Jul 03'!$A$2:$A$50)
looks correct to me. I used 50 in my formula even though
there's only 7 rows in this particular sheet, so I could
copy it to all other columns without adjusting more than
the sheet name.
=LOOKUP($A6,'Jul 03'!$B$2:$B$50,'Jul 03'!$A$2:$A$50)
is where I start getting a return value of 1 for
everything.

I cannot see the problem - help help!
.

LOOKUP finding values it should not
 

That seems to work. :)
Quick question: is there a way to make this return a 0 if a
match is not found? I thought that might be what that last
0 in the formula was for.

-----Original Message-----
Hi
try
=INDEX('Jul 03'!$A$2:$A$50,MATCH($A4,'Jul 03'!
$B$2:$B$50,0))

-----Original Message-----
I have a summary sheet for all styles of our product sold
to one company, and the actual monthly sales for each
product on separate monthly worksheets (gotten from
Access). The monthlies only have the products that
actually
sold, not the full list of products listed on the summary
page.
So I wrote a LOOKUP for the summary page to go to the
monthly, look for a match on the part number, and return
the quantity sold.
First one correctly returned a #N/A - good. I autofilled
the rest down. And got a #N/A for the second one, and then
the quantity of 1 for absolutely everything else. Only 6
products sold, though they all had a quantity of 1.

The formula
=LOOKUP($A4,'Jul 03'!$B$2:$B$50,'Jul 03'!$A$2:$A$50)
looks correct to me. I used 50 in my formula even though
there's only 7 rows in this particular sheet, so I could
copy it to all other columns without adjusting more than
the sheet name.
=LOOKUP($A6,'Jul 03'!$B$2:$B$50,'Jul 03'!$A$2:$A$50)
is where I start getting a return value of 1 for
everything.

I cannot see the problem - help help!
.

.

LOOKUP finding values it should not
 

Hi
no, the zero indicates that this formula should look for
an exact match. In your case try
=IF(ISNA(MATCH($A4,'Jul 03'!$B$2:$B$50,0)),0,INDEX('Jul
03'!$A$2:$A$50,MATCH($A4,'Jul 03'!$B$2:$B$50,0)))



-----Original Message-----
That seems to work. :)
Quick question: is there a way to make this return a 0 if
a
match is not found? I thought that might be what that last
0 in the formula was for.

-----Original Message-----
Hi
try
=INDEX('Jul 03'!$A$2:$A$50,MATCH($A4,'Jul 03'!
$B$2:$B$50,0))

-----Original Message-----
I have a summary sheet for all styles of our product
sold
to one company, and the actual monthly sales for each
product on separate monthly worksheets (gotten from
Access). The monthlies only have the products that
actually
sold, not the full list of products listed on the
summary
page.
So I wrote a LOOKUP for the summary page to go to the
monthly, look for a match on the part number, and return
the quantity sold.
First one correctly returned a #N/A - good. I autofilled
the rest down. And got a #N/A for the second one, and
then
the quantity of 1 for absolutely everything else. Only 6
products sold, though they all had a quantity of 1.

The formula
=LOOKUP($A4,'Jul 03'!$B$2:$B$50,'Jul 03'!$A$2:$A$50)
looks correct to me. I used 50 in my formula even though
there's only 7 rows in this particular sheet, so I could
copy it to all other columns without adjusting more than
the sheet name.
=LOOKUP($A6,'Jul 03'!$B$2:$B$50,'Jul 03'!$A$2:$A$50)
is where I start getting a return value of 1 for
everything.

I cannot see the problem - help help!
.

.

.