Problem with using Vlookup formula

[Montu]

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.

 

[T. Valko]

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)

Where:

A2 = product
B2 = size (like 500 Gm)

 

[Ashish Mathur]

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!D1214,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

 

[ShaneDevenshire]

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)

 

[Montu]

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.