Hi Noorain Ansari, Case-1 I have attached the same file for your reference and I have not changed anything on it just created a duplicate data.
Now look at the excel file and see what are all the results your formula is
deriving. Whether all the results are same? Take my previous mail and read
it once again which is elaborated clearly without the help of the above
attachment about the differences *[vlookup, Index-Match, Offset-Match]* and
*[Sumproduct, Sumifs, Dsum]*.
Case-II
I know sumproduct very well and the method how we are using the function
will differentiate the sumproduct from other functions. But you have used
the sumproduct for a single cell, which can be done using the simple IF
function and there is no need to go for sumproduct. I just wanted to
highlight it to you, and that was I did.
Never create and suggest formula's based on the articles you read in
internet or book, understand the concept how the functions are working and
it's concept then only you can able to give exact solution.
Let me know if you need any further clarification.
-----------------------
Ms.Exl.Learner
-----------------------
On Fri, Sep 2, 2011 at 10:38 PM, NOORAIN ANSARI <noorain.ans...@gmail.com>wrote:
> 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
>
--
----------------------------------------------------------------------------------
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 (Clarified).xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
