(Msg. 41) Posted: Thu Oct 15, 2009 9:38 am
Post subject: Re: How can I lookup when match has more than one value? [Login to view extended thread Info.] Archived from groups: microsoft>public>excel>worksheet>functions (more info?)
(Msg. 42) Posted: Wed Oct 28, 2009 6:37 am
Post subject: Re: How can I lookup when match has more than one value? [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Try the SUMIF function. It worked for me.
"Ray" wrote:
> Hi,
>
> I have a similar question on return sum of multiple vlookup values. Please
> see the following array.
>
> Item Date Amount
> a 1-Sep 10
> b 1-Sep 15
> c 1-Sep 20
> a 2-Sep 16
> c 2-Sep 21
> d 2-Sep 30
> a 3-Sep 18
> b 3-Sep 25
>
> I want the function return the sum of values which 'Item' = 'a'. I used
> =sum(vlookup(a, a2:c9, 3, false)) and it only returned the first value 10. I
> expected to get (10+16+1 = 44. How can I achieve this result? Thanks.
(Msg. 43) Posted: Thu Oct 29, 2009 1:57 pm
Post subject: Re: How can I lookup when match has more than one value? [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Is there a way to drag this formula and not enter the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER) for every row?
This formula works great for my worksheet but I need to drag it down 10,000+
rows?
> Where is the data you want returned?
>
> What is the data type of the value to be returned? Is it text? Numeric?
>
> When there are multiple lookup_values you would typically use an array
> formula** like this:
>
> =INDEX(Data!C1:C10,MATCH(1,(Data!A1:A10=A1)*(Data!B1:B10=D1),0))
>
> ** array formulas need to be entered using the key combination of
> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
> key then hit ENTER.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Hijosdelongi" <Hijosdelongi DeleteThis @discussions.microsoft.com> wrote in message
> news:0FDAB9E7-EDEE-4CBE-A802-C6CC4DB19A53@microsoft.com...
> > Hi,
> >
> > I have a Question.. if VLOOKUP plus IF possible? This is my fomula..
> >
> > =VLOOKUP(IF((Data!A1:A1000=A1)*(Data!B1:B1000=D1),....
> >
> > Can you help me with this..
> >
> > THank you.
> >
> >
> > "David Hilberg" wrote:
> >
> >> =IF( CountIf(a1:a10,"Joe")<>1, CountIf(a1:a10,"Joe"),
> >> LookUp(..etc...) )
> >>
> >> will give you the count if there are more or fewer than one.
> >> Otherwise, it performs the lookup.
> >>
> >> - David
> >>
> >> On Jul 23, 9:12 pm, bonot1 <bon... DeleteThis @discussions.microsoft.com> wrote:
> >> > I am using LOOKUP functions to retrieve info from a list. Some of the
> >> > lookup
> >> > values have more than one match in the list. Is there a function that
> >> > allows
> >> > me to retrieve multiple elements for one lookup value, or at least a
> >> > function
> >> > that tells me there are duplicate matches?
> >>
> >>
> >>
>
>
>
All times are: Eastern Time (US & Canada) (change) Goto page Previous1, 2, 3, 4, 5, 6
Page 6 of 6
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
= 44. How can I achieve this result? Thanks.
Office