Pls help me how to write a formula on multiple lookup_value".

=LOOKUP(lookup_value,lookup_vector,result_vector])

My question is based in the lookup_value. Is it possible that i can have
multiple lookup_value?

Fruit Size Weight Price
Apple m 1 0.1
Apple s 2 0.2
Grape l 3 0.2
Banana s 1 0.25
Pear m 2 0.35
Apple m 2 0.1

I like to look for the "Price" of apple(fruit) that is M(size), 1 (weight)
from the table above. How do I do it? I need an exact match for the
lookup_value

i tried a combination of lookup and concatenate formulas but is not working.
Please help me.

Thanks a lot!

The DGet function does the job and avoids the use of an array formula...
With your posted data in B5:E11 and
the following "criteria" data entered in G2:H3...
size weight
= "=M" = " =1"

This formula returns 0.1...
=DGET(B5:E11,4,G2:H3)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"Noel"
wrote in message
Pls help me how to write a formula on multiple lookup_value".
=LOOKUP(lookup_value,lookup_vector,result_vector])
My question is based in the lookup_value. Is it possible that i can have
multiple lookup_value?

Fruit Size Weight Price
Apple m 1 0.1
Apple s 2 0.2
Grape l 3 0.2
Banana s 1 0.25
Pear m 2 0.35
Apple m 2 0.1

I like to look for the "Price" of apple(fruit) that is M(size), 1 (weight)
from the table above. How do I do it? I need an exact match for the
lookup_value
i tried a combination of lookup and concatenate formulas but is not working.
Please help me.
Thanks a lot!

Hi Jim,

Thanks for your reply but it seems that it's a partial answer to my question
or i may not be getting you. Let me try again.

Fruit Size Wt Price
Apple m 1 0.1
Apple s 2 0.2
Grape l 3 0.2
Banana s 1 0.25
Pear m 2 0.35
Apple m 2 0.1


Based on the table above, my customer asks me what's the price of aN APPLE,
M size and weight is 2. I may have several entries of APLLES with different
SIZES and WEIGHTs but I need the price for a specific condition/criteria
given.

Thanks again.






"Jim Cone" wrote:


The DGet function does the job and avoids the use of an array formula...
With your posted data in B5:E11 and
the following "criteria" data entered in G2:H3...
size weight
= "=M" = " =1"

This formula returns 0.1...
=DGET(B5:E11,4,G2:H3)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"Noel"
wrote in message
Pls help me how to write a formula on multiple lookup_value".
=LOOKUP(lookup_value,lookup_vector,result_vector])
My question is based in the lookup_value. Is it possible that i can have
multiple lookup_value?

Fruit Size Weight Price
Apple m 1 0.1
Apple s 2 0.2
Grape l 3 0.2
Banana s 1 0.25
Pear m 2 0.35
Apple m 2 0.1

I like to look for the "Price" of apple(fruit) that is M(size), 1 (weight)
from the table above. How do I do it? I need an exact match for the
lookup_value
i tried a combination of lookup and concatenate formulas but is not working.
Please help me.
Thanks a lot!

Try:
=SUMPRODUCT((A2:A7="Apple")*(B2:B7="m")*(C2:C7=2)* D2:D7)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Noel" wrote in message
...
Hi Jim,

Thanks for your reply but it seems that it's a partial answer to my
question
or i may not be getting you. Let me try again.

Fruit Size Wt Price
Apple m 1 0.1
Apple s 2 0.2
Grape l 3 0.2
Banana s 1 0.25
Pear m 2 0.35
Apple m 2 0.1


Based on the table above, my customer asks me what's the price of aN
APPLE,
M size and weight is 2. I may have several entries of APLLES with
different
SIZES and WEIGHTs but I need the price for a specific condition/criteria
given.

Thanks again.

"Sandy Mann" wrote...
Try:
=SUMPRODUCT((A2:A7="Apple")*(B2:B7="m")*(C2:C7=2) *D2:D7)
....

I'm not a purist about separating all terms in SUMPRODUCT, but there's some
value in separating the values summed from the criteria, so

=SUMPRODUCT((A2:A7="Apple")*(B2:B7="m")*(C2:C7=2), D2:D7)

just in case D2:D7 contained anything that wasn't numeric. The conditions
don't require such treatment because Excel can compare numbers, text and
boolean values without returning errors.

Harlan,
One of the many things I didn't know - glad you posted it.

One, maybe obvious, comment is that in the case of duplicate entries the
SumProduct formula returns the sum of the duplicates (an incorrect answer),
while the Database function returns an error value.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"Harlan Grove"
wrote in message
"Sandy Mann" wrote...
Try:
=SUMPRODUCT((A2:A7="Apple")*(B2:B7="m")*(C2:C7=2) *D2:D7)
....
I'm not a purist about separating all terms in SUMPRODUCT, but there's some
value in separating the values summed from the criteria, so

=SUMPRODUCT((A2:A7="Apple")*(B2:B7="m")*(C2:C7=2), D2:D7)

just in case D2:D7 contained anything that wasn't numeric. The conditions
don't require such treatment because Excel can compare numbers, text and
boolean values without returning errors.

Harlan Grove" wrote in message
...

I'm not a purist about separating all terms in SUMPRODUCT, but there's
some value in separating the values summed from the criteria, so

Debatable point. There seems to be a trend for people in the NG's to
recommend comma separation which, I have read, is slighly faster but I have
always been of the opinion that I would rather see an error returned than a
zero which may go unnoticed. But thank you for your insight nevertheless.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

Yes, I left out one of the criteria fields, so in F2:H3...
fruit size weight
apple = "=M" = " =2"

With a formula of
=DGET(B5:E11,4,F2:H3)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Noel"
wrote in message
Hi Jim,
Thanks for your reply but it seems that it's a partial answer to my question
or i may not be getting you. Let me try again.

Fruit Size Wt Price
Apple m 1 0.1
Apple s 2 0.2
Grape l 3 0.2
Banana s 1 0.25
Pear m 2 0.35
Apple m 2 0.1


Based on the table above, my customer asks me what's the price of aN APPLE,
M size and weight is 2. I may have several entries of APLLES with different
SIZES and WEIGHTs but I need the price for a specific condition/criteria
given.
Thanks again.






"Jim Cone" wrote:

The DGet function does the job and avoids the use of an array formula...
With your posted data in B5:E11 and
the following "criteria" data entered in G2:H3...
size weight
= "=M" = " =1"

This formula returns 0.1...
=DGET(B5:E11,4,G2:H3)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"Noel"
wrote in message
Pls help me how to write a formula on multiple lookup_value".
=LOOKUP(lookup_value,lookup_vector,result_vector])
My question is based in the lookup_value. Is it possible that i can have
multiple lookup_value?

Fruit Size Weight Price
Apple m 1 0.1
Apple s 2 0.2
Grape l 3 0.2
Banana s 1 0.25
Pear m 2 0.35
Apple m 2 0.1

I like to look for the "Price" of apple(fruit) that is M(size), 1 (weight)
from the table above. How do I do it? I need an exact match for the
lookup_value
i tried a combination of lookup and concatenate formulas but is not working.
Please help me.
Thanks a lot!

Try,

=SUMPRODUCT((A2:A20="Apple")*(B2:B20="m")*(C2:C20= 1)*(D2:D20))

Mike

"Noel" wrote:

Pls help me how to write a formula on multiple lookup_value".

=LOOKUP(lookup_value,lookup_vector,result_vector])

My question is based in the lookup_value. Is it possible that i can have
multiple lookup_value?

Fruit Size Weight Price
Apple m 1 0.1
Apple s 2 0.2
Grape l 3 0.2
Banana s 1 0.25
Pear m 2 0.35
Apple m 2 0.1

I like to look for the "Price" of apple(fruit) that is M(size), 1 (weight)
from the table above. How do I do it? I need an exact match for the
lookup_value

i tried a combination of lookup and concatenate formulas but is not working.
Please help me.

Thanks a lot!

"Fruit","Size" and "Weight" are defined name ranges

=INDEX(Price,MATCH("Apple"&"m"&1,INDEX(Fruit&Size& Weight,0),0))


"Noel" wrote:

Pls help me how to write a formula on multiple lookup_value".

=LOOKUP(lookup_value,lookup_vector,result_vector])

My question is based in the lookup_value. Is it possible that i can have
multiple lookup_value?

Fruit Size Weight Price
Apple m 1 0.1
Apple s 2 0.2
Grape l 3 0.2
Banana s 1 0.25
Pear m 2 0.35
Apple m 2 0.1

I like to look for the "Price" of apple(fruit) that is M(size), 1 (weight)
from the table above. How do I do it? I need an exact match for the
lookup_value

i tried a combination of lookup and concatenate formulas but is not working.
Please help me.

Thanks a lot!

I would insert a blank column (d) set up a composite index in an blank column
(D)
D2=A2 & "-" and B2 & "-" & C2 then copy down the column.

You can then use vlookup to find the value you want

Ex vlookup(d2:e20,"Apple-m-1",2,False)


A B C D E
Fruit Size Weight Index Price
Apple m 1 0.1
Apple s 2 0.2
Grape l 3 0.2
Banana s 1 0.25
Pear m 2 0.35
Apple m 2 0.1





"Noel" wrote:

Pls help me how to write a formula on multiple lookup_value".

=LOOKUP(lookup_value,lookup_vector,result_vector])

My question is based in the lookup_value. Is it possible that i can have
multiple lookup_value?


Fruit Size Weight Price
Apple m 1 0.1
Apple s 2 0.2
Grape l 3 0.2
Banana s 1 0.25
Pear m 2 0.35
Apple m 2 0.1

I like to look for the "Price" of apple(fruit) that is M(size), 1 (weight)
from the table above. How do I do it? I need an exact match for the
lookup_value

i tried a combination of lookup and concatenate formulas but is not working.
Please help me.

Thanks a lot!