Multiple "lookup_value"

[Noel]

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!

 

[Jim Cone]

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" <[email protected]>
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!

 

[Mike H]

Try,

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

Mike

 

[Teethless mama]

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

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

 

[Noel]

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]

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

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Jim Cone]

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" <[email protected]>
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.

 

[Older-but-better]

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

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!

 

[Harlan Grove]

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

....

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),D27)

just in case D27 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.

 

[Jim Cone]

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" <[email protected]>
wrote in message

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

....
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),D27)

just in case D27 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]

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.

....

You mean DGET? Having used (though sadly no more) Lotus 123 Release 3 and
later versions, I just can't take Excel's equivalents for 123 Release 2.x
@D... functions seriously, so I'd never suggest anyone else use them. Using
a criteria range instead of criteria expressions is just too fragile and
inflexible.

There's always a workaround if you want to check for existence and
uniqueness.

=CHOOSE(1+MIN(SUMPRODUCT((A2:A7="Apple")*(B2:B7="m")*(C2:C7=2)),2),
#NULL!,SUMPRODUCT((A2:A7="Apple")*(B2:B7="m")*(C2:C7=2),D27),#VALUE!)

Downside is that this reads through the criteria ranges twice.

However, I don't see this as a practical concern in spreadsheets. Unique
value lookups aren't even guaranteed in databases except when the lookup is
on key fields. Spreadsheet tables are even less likely to have key fields
than database tables, so I wouldn't expect even multiple field lookups to
produce just one or zero matches.

 

[Harlan Grove]

However, I don't see this as a practical concern in spreadsheets. Unique
value lookups aren't even guaranteed in databases except when the lookup
is on key fields. Spreadsheet tables are even less likely to have key
fields than database tables, so I wouldn't expect even multiple field
lookups to produce just one or zero matches.

I think I missed the point that this was a lookup problem.

If the last value would be as acceptable as the first, there's

=LOOKUP(2,1/(A2:A7="Apple")/(B2:B7="m")/(C2:C7=2),D27)

but if the first match is preferable, better to use INDEX(..,MATCH(..)) as
already proposed.

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Harlan Grove]

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. . . .

When have I ever shied away from a debatable point?!

Depends on whether one wants a numeric result from

=SUMPRODUCT((Condition1)*...*(ConditionN),RangeToBeSummed)

when

=SUM(RangeToBeSummed)

produces a numeric result. If so, then better to use commas. If not, then it
begs the question whether simple SUM formulas should always be changed to

=SUMPRODUCT(--RangeToBeSummed)

to catch errors all the time. Consistency is a virtue.

 

[Sandy Mann]

When have I ever shied away from a debatable point?!

You Harlan - never!!

to catch errors all the time. Consistency is a virtue

So your argument is that because SUM() ignores text, all functions should
ignore text?

Perhaps if we are using it as a SUM() function, but here are we not in fact
using, (or misusing), it as a VLOOKUP() which does return errors at times.
So in that instance is it not more consistent to return errors than to
ignore them?

--
Regards,

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

<snip> (this time<g>)

I should of course finished with:

Listening out

<g>

--
Regards,

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Harlan Grove]

So your argument is that because SUM() ignores text, all functions should
ignore text?

No. You're oversimplifying.

My argument is that ignoring text in conditional summing is as desirable as
ignoring text in nonconditional summing. That is, if

=SUM(Range)

produces a numeric result (and it always will unless there are error values
in some of the cells in Range), and since

=SUMIF(OtherRange,Criterion,Range)

would then also produce a numeric result NO MATTER WHAT'S IN OtherRange as
long as it's the same size/shape as Range, then consistency would make it
desirable for

=SUMPRODUCT((Condition1)*...*(ConditionN),Range)

also to produce a numeric result.

Perhaps if we are using it as a SUM() function, but here are we not in
fact using, (or misusing), it as a VLOOKUP() which does return errors at
times. So in that instance is it not more consistent to return errors
than to ignore them?

....

Ah, yes, this entire thread was originally about lookups. And if there were
nonnumeric text in some record that DIDN'T match the criteria but a number
in the record that DID match the criteria, what should the formula return?

But if there were text in the field sought for the record matching the
criteria, would it be better to return the text value or #VALUE! ?

But that all ignores the point Jim Cone brought up: SUMPRODUCT fails
miserably when there could be multiple records matching the criteria. For
lookup tasks, use lookup functions or MATCH.

Your use of SUMPRODUCT was ill-considered for lookup tasks, and it's
suboptimal/inconsistent for conditional summing.

 

[Harlan Grove]

. . . it as a VLOOKUP() which does return errors at times.

....

VLOOKUP returns errors only if (1) there's no lookup value found, or (2)
there's an error value in the return value column in the record matching the
lookup value. Your formula will reproduce the second result, but would
unhappily also return error values if there were any error values anywhere
in any of the ranges you're processing, which is UNLIKE VLOOKUP behavior.
OTOH, if there were no error values in any of the ranges and only numbers or
blank cells in the result range, your formula would return 0 rather than an
error value. For a lookup operation, there should be a clearer indication of
failing to find a match.

 

[Sandy Mann]

One day Harlan.......... One day.......

<g>

--
Regards,

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk