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
