Help with lookup
 

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

Help with lookup
 

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

Help with lookup
 

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

Help with lookup
 

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

Help with lookup
 

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