Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple "lookup_value"
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple "lookup_value"
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple "lookup_value"
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple "lookup_value"
"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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple "lookup_value"
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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple "lookup_value"
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple "lookup_value"
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! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple "lookup_value"
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! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple "lookup_value"
"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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple "lookup_value"
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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple "lookup_value"
"Jim Cone" wrote...
.... 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),D2:D7),#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. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple "lookup_value"
"Harlan Grove" wrote...
.... 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),D2:D7) but if the first match is preferable, better to use INDEX(..,MATCH(..)) as already proposed. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple "lookup_value"
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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple "lookup_value"
"Sandy Mann" wrote...
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. . . . When have I ever shied away from a debatable point?! Depends on whether one wants a numeric result from =SUMPRODUCT((Condition1)*...*(ConditionN),RangeToB eSummed) 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. |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple "lookup_value"
"Sandy Mann" wrote in message
... <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 Replace @mailinator.com with @tiscali.co.uk |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple "lookup_value"
"Sandy Mann" wrote...
.... 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. |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple "lookup_value"
"Sandy Mann" wrote...
.... . . . 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. |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple "lookup_value"
One day Harlan.......... One day.......
<g -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Combining formulas, "and" & "or" to verify content of multiple cel | Excel Discussion (Misc queries) | |||
"Control" plus "click" doesn't allow me to select multiple cells | New Users to Excel | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
freeze window creates multiple "views" suffixed with ":n" | Excel Discussion (Misc queries) |