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.

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.

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.

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.

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.