Dear Ms-Exl-Learner, Thanks for your valuable suggestion.. Case -I, In case of duplicay all formulas are successfull working except vlookup(vlookup) example. You can see fresh attachement..
Case -II, You can't compare Sumproduct with If function both are
different..
Correct Syntex of Formula Should be..
=SUMPRODUCT((C3:C8=G3)*(D3:D8=H3)*E3:E8) .instead of
=SUMPRODUCT((F3=J3)*(G3=K3)*H3)
another way :
*{=LOOKUP(2,1/(C3:C8=G3)*(D3:D8=H3),E3:E8)}*
Keep Enjoy, Cheers..
--
Thanks & regards,
Noorain Ansari
*http://noorain-ansari.blogspot.com/* <http://noorain-ansari.blogspot.com/>
On Fri, Sep 2, 2011 at 9:51 PM, Ms-Exl-Learner .
<ms.exl.lear...@gmail.com>wrote:
> Hi Noorain Ansari,
>
> It might be better if you might have constructed your example data with
> some duplicates, since the questioner can able to understand the difference
> between the *[vlookup, Index-Match, Offset-Match]* and *[Sumproduct,
> Sumifs, Dsum]*.
>
> The *First set of formula's / Functions* just get the first match as
> result and never consider the next matches. But the *Second
> functions*consider all the matches and derive the result by adding the values
> of the
> all matches.
>
> Please clarify me why the below formula should not be written
> as =IF((F3=J3)*(G3=K3),H3,0) or =IF(AND((F3=J3),(G3=K3)),H3,0)???
>
> =SUMPRODUCT((F3=J3)*(G3=K3)*H3)
>
> You have provided the below formula to avoid the K3 cell criteria to be
> automated by the formula
> =VLOOKUP(VLOOKUP($J$3,$F$3:$G$8,2,0),$G$3:$H$8,2,0)
> But,
> It won't work fine when the K3 cell is having the 31-Aug as criteria and
> the G5 cell have the 31-Aug.
>
> -----------------------
> Ms.Exl.Learner
> -----------------------
>
> On Fri, Sep 2, 2011 at 8:51 PM, NOORAIN ANSARI <noorain.ans...@gmail.com
> > wrote:
>
>> Dear Haytham,
>>
>> Please try below formula :
>>
>> *=VLOOKUP(J3&K3,$E$3:$H$3,4,0)*
>> *=VLOOKUP(VLOOKUP($J$3,$F$3:$G$8,2,0),$G$3:$H$8,2,0)*
>> other Alternative.......
>>
>> *=SUMPRODUCT((F3=J3)*(G3=K3)*H3)*
>> *{=INDEX($H$3:$H$8,MATCH(1,($F$3:$F$8=$J$3)*($G$3:$G$8=$K$3),0))}*
>> *{=OFFSET($H$2,MATCH(1,($F$3:$F$8=$J$3)*($G$3:$G$8=$K$3),0),0)}
>> =SUMIFS(H3:H8,F3:F8,J3,G3:G8,K3)*
>> *=DSUM($F$2:$H$8,H2,J2:K3)*
>>
>>
>> See attached sheet..
>> --
>> Thanks & regards,
>> Noorain Ansari
>> *http://excelmacroworld.blogspot.com/*
>> *http://noorain-ansari.blogspot.com/*<http://noorain-ansari.blogspot.com/>
>>
>>
>> On Fri, Sep 2, 2011 at 5:02 PM, Haytham Zoromba <
>> haythamzoro...@gmail.com> wrote:
>>
>>> Dear all,
>>>
>>> I have tried to search about using vlookup with more than one condition.
>>>
>>> Is their any one know a formula for that?
>>>
>>>
>>> BRegards,
>>> Haytham Zoromba
>>>
>>> --
>>>
>>> ----------------------------------------------------------------------------------
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> Like our page on facebook , Just follow below link
>>> http://www.facebook.com/discussexcel
>>>
>>
>>
>>
>>
>> --
>>
>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Multiple_Vlookup_Example(Updated).xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
