Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with using Vlookup formula
I have a Price List in Sheet1 Like
A B C 1 Product Pack Rate 2 Mango Jam 500 Gm 20.00 3 Mango Jam 250 Gm 18.00 4 Pinapple Jelly 500 Gm 35.00 5 Pinapple Jelly 250 Gm 25.00 Continued...to Last In Sheet2 I A2 = Validation List of all Pruduct B2 = validate formula is =Indirect(a1) C2 = formula is =vlookup(b2,rate,2,0) The problem is that every time it shows the rate of first pack (i.e. mango 500gm or 250gm), if I choose other product in A2 also. But I want to put rate automatically in C2 according my Product and Pack selection. Is there any solution for above. Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with using Vlookup formula
B2 = validate formula is =Indirect(a1)
What does that do? Does it populate a drop down list with the "pack sizes" for the product selected in A2? You need a formula like this: =SUMPRODUCT(--(Sheet1!A1:A10=A2),--(Sheet1!B1:B10=B2),Sheet1!C1:C10) Whe A2 = product B2 = size (like 500 Gm) -- Biff Microsoft Excel MVP "Montu" wrote in message ... I have a Price List in Sheet1 Like A B C 1 Product Pack Rate 2 Mango Jam 500 Gm 20.00 3 Mango Jam 250 Gm 18.00 4 Pinapple Jelly 500 Gm 35.00 5 Pinapple Jelly 250 Gm 25.00 Continued...to Last In Sheet2 I A2 = Validation List of all Pruduct B2 = validate formula is =Indirect(a1) C2 = formula is =vlookup(b2,rate,2,0) The problem is that every time it shows the rate of first pack (i.e. mango 500gm or 250gm), if I choose other product in A2 also. But I want to put rate automatically in C2 according my Product and Pack selection. Is there any solution for above. Thanks in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with using Vlookup formula
Hi,
How are you? I am not quite clear about your question. In cell B2, if you have the pack size, then the formula in C2 should with respect to both product and pack and I do not think you can use a VLOOKUP for this. You could try to use this formula INDEX(Sheet1!$C$12:$D$14,MATCH(Sheet2!A2&Sheet2!B2 ,Sheet1!D12:D14,0),1) Sheet1!D12 has the formula A13*B13. I assume that you have product in A13 and pack in B13. -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Montu" wrote in message ... I have a Price List in Sheet1 Like A B C 1 Product Pack Rate 2 Mango Jam 500 Gm 20.00 3 Mango Jam 250 Gm 18.00 4 Pinapple Jelly 500 Gm 35.00 5 Pinapple Jelly 250 Gm 25.00 Continued...to Last In Sheet2 I A2 = Validation List of all Pruduct B2 = validate formula is =Indirect(a1) C2 = formula is =vlookup(b2,rate,2,0) The problem is that every time it shows the rate of first pack (i.e. mango 500gm or 250gm), if I choose other product in A2 also. But I want to put rate automatically in C2 according my Product and Pack selection. Is there any solution for above. Thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with using Vlookup formula
Hi,
Your VLOOKUP formula only can handle a single criteria the way you are using it. =SUMPRODUCT(--(A2=Sheet1!A$2:A$100),--(B2=Sheet1!B$2:B$100),CS2:CS100) -- Thanks, Shane Devenshire "Montu" wrote: I have a Price List in Sheet1 Like A B C 1 Product Pack Rate 2 Mango Jam 500 Gm 20.00 3 Mango Jam 250 Gm 18.00 4 Pinapple Jelly 500 Gm 35.00 5 Pinapple Jelly 250 Gm 25.00 Continued...to Last In Sheet2 I A2 = Validation List of all Pruduct B2 = validate formula is =Indirect(a1) C2 = formula is =vlookup(b2,rate,2,0) The problem is that every time it shows the rate of first pack (i.e. mango 500gm or 250gm), if I choose other product in A2 also. But I want to put rate automatically in C2 according my Product and Pack selection. Is there any solution for above. Thanks in advance. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with using Vlookup formula
Thanks for your advice, but one things I could not able to understand, i.e.
"Sheet1!D12 has the formula A13*B13", why not use in C12, and another things is that why I use the formula A13*B13 ? If you clear it me that should be very much helpfull to me to understand. Thank you again. "Ashish Mathur" wrote: Hi, How are you? I am not quite clear about your question. In cell B2, if you have the pack size, then the formula in C2 should with respect to both product and pack and I do not think you can use a VLOOKUP for this. You could try to use this formula INDEX(Sheet1!$C$12:$D$14,MATCH(Sheet2!A2&Sheet2!B2 ,Sheet1!D12:D14,0),1) Sheet1!D12 has the formula A13*B13. I assume that you have product in A13 and pack in B13. -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Montu" wrote in message ... I have a Price List in Sheet1 Like A B C 1 Product Pack Rate 2 Mango Jam 500 Gm 20.00 3 Mango Jam 250 Gm 18.00 4 Pinapple Jelly 500 Gm 35.00 5 Pinapple Jelly 250 Gm 25.00 Continued...to Last In Sheet2 I A2 = Validation List of all Pruduct B2 = validate formula is =Indirect(a1) C2 = formula is =vlookup(b2,rate,2,0) The problem is that every time it shows the rate of first pack (i.e. mango 500gm or 250gm), if I choose other product in A2 also. But I want to put rate automatically in C2 according my Product and Pack selection. Is there any solution for above. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with copy/paste VLOOKUP formula | Excel Discussion (Misc queries) | |||
PROBLEM WITH INDIRECT & VLOOKUP FORMULA | Excel Worksheet Functions | |||
Vlookup/If? Formula problem? | New Users to Excel | |||
Problem with VLOOKUP formula | Excel Worksheet Functions | |||
Vlookup, What is correct formula for problem below? | Excel Worksheet Functions |