(Msg. 33) Posted: Fri Aug 14, 2009 3:15 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?)
Hi Valko,
I have something similar and I am just used to vlookup for one single value
to compare two columns so I get value in column2 returned.
Now I do have two columns plus a condition and three columns, and I would
like to get the value in column3 returned.
example:
ID name shs test results ID name shs
1234 Timo 13 D2 1225 Timo 20
1225 Timo 20 D3 1234 Timo 10
I thought vlookup can help if (A2+A3) match (E2+F2), then return the value
in column G.
Result: Cell D2 should get value "10" (G3) and Cell D3 should get the value
"20" (G2).
Thank you so much in advance.
Regards, Timo
"T. Valko" wrote:
> Try this:
>
> ...........A..........B..........C
> 1.......Bob.......x.........AA
> 2.......Sue.......x..........BB
> 3.......Bob.......z.........CC
> 4.......Sue.......a.........GG
> 5.......Tom......h.........FF
>
> To lookup "Sue" and "a":
>
> E1 = Sue
> F1 = a
>
> Array entered** :
>
> =INDEX(C1:C5,MATCH(1,(A1:A5=E1)*(B1:B5=F1),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 RemoveThis @discussions.microsoft.com> wrote in message
> news:3D38CD88-BAC1-4C17-B9FE-F6E3FC45FA03@microsoft.com...
> > Actually the data that i want to be returned is both numeric and text
> > thats
> > why i had IF function.. in a typical VLOOKUP formula is should be like
> > this..
> >
> > =VLOOKUP(B1,Data!A1:A1000,2,FALSE)
> >
> > but since i only want to have the data that is equivalent to the date and
> > the name on the database thats why im to use this formula..
> >
> > =VLOOKUP(IF((Data!A1:A1000=A1)*(Data!B1:B1000=D1),....
> >
> > that is also why im having problems in how to formulate the conditions in
> > the IF function and how to get the data using the VLOOKUP.
> >
> > I hope you can help me.
> >
> > Thank you so much.
> >
> >
> >
> >
> > "T. Valko" wrote:
> >
> >> 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 RemoveThis @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... RemoveThis @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?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
(Msg. 34) Posted: Fri Aug 14, 2009 1:14 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?)
"Timo" <Timo.DeleteThis@discussions.microsoft.com> wrote in message
news:A820E820-BBF8-4394-BE0E-59293CF5D309@microsoft.com...
> Hi Valko,
>
> I have something similar and I am just used to vlookup for one single
> value
> to compare two columns so I get value in column2 returned.
>
> Now I do have two columns plus a condition and three columns, and I would
> like to get the value in column3 returned.
>
> example:
>
> ID name shs test results ID name shs
>
> 1234 Timo 13 D2 1225 Timo 20
> 1225 Timo 20 D3 1234 Timo 10
>
>
> I thought vlookup can help if (A2+A3) match (E2+F2), then return the value
> in column G.
>
> Result: Cell D2 should get value "10" (G3) and Cell D3 should get the
> value
> "20" (G2).
>
> Thank you so much in advance.
>
> Regards, Timo
>
>
> "T. Valko" wrote:
>
>> Try this:
>>
>> ...........A..........B..........C
>> 1.......Bob.......x.........AA
>> 2.......Sue.......x..........BB
>> 3.......Bob.......z.........CC
>> 4.......Sue.......a.........GG
>> 5.......Tom......h.........FF
>>
>> To lookup "Sue" and "a":
>>
>> E1 = Sue
>> F1 = a
>>
>> Array entered** :
>>
>> =INDEX(C1:C5,MATCH(1,(A1:A5=E1)*(B1:B5=F1),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:3D38CD88-BAC1-4C17-B9FE-F6E3FC45FA03@microsoft.com...
>> > Actually the data that i want to be returned is both numeric and text
>> > thats
>> > why i had IF function.. in a typical VLOOKUP formula is should be like
>> > this..
>> >
>> > =VLOOKUP(B1,Data!A1:A1000,2,FALSE)
>> >
>> > but since i only want to have the data that is equivalent to the date
>> > and
>> > the name on the database thats why im to use this formula..
>> >
>> > =VLOOKUP(IF((Data!A1:A1000=A1)*(Data!B1:B1000=D1),....
>> >
>> > that is also why im having problems in how to formulate the conditions
>> > in
>> > the IF function and how to get the data using the VLOOKUP.
>> >
>> > I hope you can help me.
>> >
>> > Thank you so much.
>> >
>> >
>> >
>> >
>> > "T. Valko" wrote:
>> >
>> >> 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?
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>
(Msg. 35) Posted: Wed Sep 16, 2009 11:22 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?)
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. 36) Posted: Wed Sep 16, 2009 2:50 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?)
=sumif(a:a,"B",c:c)
I changed the string to B just so there wouldn't be so many A's in the formula.
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. 37) Posted: Fri Sep 25, 2009 5:40 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?)
Hi, I have the same query i.e. sum of multiple vlookups....I'd be very
interested to get advice
"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. 38) Posted: Fri Sep 25, 2009 11:33 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?)
I had similar issue and surfed this website to find the solution but then I
trued used SumIF and it worked for me. For you it shoould be sumif(A1:A8, a,
C1:C. Hope it works for you as well. Good luck.
Cheers,
Abid
"Dave Peterson" wrote:
> =sumif(a:a,"B",c:c)
>
> I changed the string to B just so there wouldn't be so many A's in the formula.
>
>
>
> 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.
>
> --
>
> Dave Peterson
>
(Msg. 39) Posted: Wed Oct 07, 2009 7:57 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?)
That should just be a simple sumif:
=SUMIF($A$1:$C$8,"a",$C$1:$C$
"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. 40) Posted: Thu Oct 15, 2009 8:23 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?)
All times are: Eastern Time (US & Canada) (change) Goto page Previous1, 2, 3, 4, 5, 6, 7
Page 5 of 7
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