I need help with lookup.

Sheet 1 list with 3 columns: A= Product Category B= Product Codes C= Quantity
Sheet 2 list with 2 columns: A= Product Category B= Fee

I want to have on sheet 1 Column D a calculation of quantity X fee (for that
Product Category)
This is what I have in D2 =IF(A2=Sheet2!$A:$A,C2*Sheet2!$B:$B)
It works for some of the rows but most of them I get FALSE.

I think I need to do a VLOOKUP (or something else)but I don't know how.
Thanks

Try:
=SUMIF(Sheet1!$A$2:$A$1000,A2,Sheet1!$C$2:$C$1000) *B2

This will sum up the entire quantity for a given product category in A2
(assuming you have multiple product category listings on sheet1) and then
multiply by the product category fee in B2.
--
** John C **

"fruitchunk" wrote:

I need help with lookup.

Sheet 1 list with 3 columns: A= Product Category B= Product Codes C= Quantity
Sheet 2 list with 2 columns: A= Product Category B= Fee

I want to have on sheet 1 Column D a calculation of quantity X fee (for that
Product Category)
This is what I have in D2 =IF(A2=Sheet2!$A:$A,C2*Sheet2!$B:$B)
It works for some of the rows but most of them I get FALSE.

I think I need to do a VLOOKUP (or something else)but I don't know how.
Thanks

Assuming first Product Cat on Sheet1 is in A2
In D2 enter =C2*VLOOKUP(A2,Sheet2!A:B,2,FALSE)
This part =VLOOKUP(A2,Sheet2!A:B,2,FALSE) looks up the fee on sheet2
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"fruitchunk" wrote in message
...
I need help with lookup.

Sheet 1 list with 3 columns: A= Product Category B= Product Codes C=
Quantity
Sheet 2 list with 2 columns: A= Product Category B= Fee

I want to have on sheet 1 Column D a calculation of quantity X fee (for
that
Product Category)
This is what I have in D2 =IF(A2=Sheet2!$A:$A,C2*Sheet2!$B:$B)
It works for some of the rows but most of them I get FALSE.

I think I need to do a VLOOKUP (or something else)but I don't know how.
Thanks

This doesn't work for me. I get #VALUE.
The formulaI got from Bernard Liengme works fine.

Thanks anyway

"John C" wrote:

Try:
=SUMIF(Sheet1!$A$2:$A$1000,A2,Sheet1!$C$2:$C$1000) *B2

This will sum up the entire quantity for a given product category in A2
(assuming you have multiple product category listings on sheet1) and then
multiply by the product category fee in B2.
--
** John C **

"fruitchunk" wrote:

I need help with lookup.

Sheet 1 list with 3 columns: A= Product Category B= Product Codes C= Quantity
Sheet 2 list with 2 columns: A= Product Category B= Fee

I want to have on sheet 1 Column D a calculation of quantity X fee (for that
Product Category)
This is what I have in D2 =IF(A2=Sheet2!$A:$A,C2*Sheet2!$B:$B)
It works for some of the rows but most of them I get FALSE.

I think I need to do a VLOOKUP (or something else)but I don't know how.
Thanks

The formula worked fine for me using the criteria you gave. It would be
interesting to find out where the error occurs using formula auditing.
--
** John C **


"fruitchunk" wrote:

This doesn't work for me. I get #VALUE.
The formulaI got from Bernard Liengme works fine.

Thanks anyway

"John C" wrote:

Try:
=SUMIF(Sheet1!$A$2:$A$1000,A2,Sheet1!$C$2:$C$1000) *B2

This will sum up the entire quantity for a given product category in A2
(assuming you have multiple product category listings on sheet1) and then
multiply by the product category fee in B2.
--
** John C **

"fruitchunk" wrote:

I need help with lookup.

Sheet 1 list with 3 columns: A= Product Category B= Product Codes C= Quantity
Sheet 2 list with 2 columns: A= Product Category B= Fee

I want to have on sheet 1 Column D a calculation of quantity X fee (for that
Product Category)
This is what I have in D2 =IF(A2=Sheet2!$A:$A,C2*Sheet2!$B:$B)
It works for some of the rows but most of them I get FALSE.

I think I need to do a VLOOKUP (or something else)but I don't know how.
Thanks