Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |