(Msg. 25) Posted: Mon Jul 13, 2009 12: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: microsoft>public>excel>worksheet>functions (more info?)
>i still dont understand the 1 and the 0?...
MATCH(1,(A1:A5=E1)*(B1:B5=F1),0)
The 1 is the lookup_value and the 0 means we want to find an exact match of
the lookup_value.
Based on the sample I posted...
(A1:A5=E1)*(B1:B5=F1)
When both conditions are TRUE the result will be 1.
The result of MATCH is 4 and is passed to the INDEX function meaning we want
the 4th element of the indexed range C1:C5.
=INDEX(C1:C5,4)
=INDEX({"AA";"BB";"CC";"GG";"FF"},4)
GG is the 4th element of the indexed range so the result of the formula is
GG
E1 = Sue
F1 = a
=INDEX(C1:C5,MATCH(1,(A1:A5=E1)*(B1:B5=F1),0))
=GG
--
Biff
Microsoft Excel MVP
"Hijosdelongi" <Hijosdelongi.RemoveThis@discussions.microsoft.com> wrote in message
news:62BD29FC-470A-4873-AD42-332E798DC6C2@microsoft.com...
> IC.. Got it, C1:C5.. but i still dont understand the 1 and the 0?...
>
> Thank you.
>
>
>
> "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. 26) Posted: Mon Jul 13, 2009 1:07 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?)
"Hijosdelongi" <Hijosdelongi.DeleteThis@discussions.microsoft.com> wrote in message
news:166CE749-FBDB-4190-80BA-A5EF8DEF6074@microsoft.com...
> Got it!!! Thank you so much for your help! Your really the best!!!
>
> last question, if im going to add additional condition, ill just use this
> formula, right?
>
> =INDEX(Data!C1:Data!C5,MATCH(1,(Data!A1:Data!A5=E1)*(Data!B1:Data!B5=F1)*(????????????),0))
>
> is it correct?
>
> Thank you so much!!!
>
> =)
>
>
>
> "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. 27) Posted: Thu Jul 23, 2009 4:43 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?)
I am currently having to set up a excel spreadsheet for a ski lodge business
and there are several issues which are very confusing.
I am using a vlookup function and it is loooking up and selecting text, but
it is selecting the first text only because ther are several different values
with the same value, but i would rather it display more than one answer. is
there a variation of the vlookup function which shows two possible answers or
is there another function which is better suited??
"bonot1" 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. 28) Posted: Mon Jul 27, 2009 11:36 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 have a flowup question: how can I return all of match up cells in my
spreadsheet?
"bonot1" 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. 29) Posted: Tue Jul 28, 2009 6:59 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?)
This is very helpful to me also; however, I have a need to return every match
to a specific cell or in a continuous stream in one cell. The matches are
names that match a particular number. Is there a way to print in columns
specified, each match to the number being looked up?
"T. Valko" wrote:
> Here's one way:
>
> Assume data in A2:B20. You want to extract data from column B that
> corresponds to a lookup_value.
>
> D2 = lookup_value
>
> Array entered** :
>
> =IF(ROWS($1:1)<=COUNTIF(A$2:A$20,D$2),INDEX(B$2:B$20,SMALL(IF(A$2:A$20=D$2,ROW(B$2:B$20)-MIN(ROW(B$2:B$20))+1),ROWS($1:1))),"")
>
> Copy down until you get blanks.
>
> ** array formulas need to be entered using the key combination of
> CTRL,SHIFT,ENTER (not just ENTER)
>
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "bonot1" <bonot1 RemoveThis @discussions.microsoft.com> wrote in message
> news:8D2A39C6-255D-4110-95C6-44D3AAB4309D@microsoft.com...
> > Data is in random order, and the data to be returned is text.
> >
> > "T. Valko" wrote:
> >
> >> Is the data sorted so that the lookup_values are grouped together or is
> >> the
> >> data random? Is the data to be returned text or numeric?
> >>
> >> --
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "bonot1" <bonot1 RemoveThis @discussions.microsoft.com> wrote in message
> >> news:34F33288-D831-4FE6-89B6-657986F9255E@microsoft.com...
> >> >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. 30) Posted: Tue Jul 28, 2009 10:58 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'm not sure how to word this exactly, but I have one sheet like this:
a b c d
red 12 11 9
blue 9 4 7
yellow 21 16 5
red 3 13 11
And I want to return the rows of just 'red' entries on another sheet,
without blank rows in the new sheet, like this:
(Msg. 31) Posted: Wed Jul 29, 2009 8:05 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?)
So I can help can you advise re the following:
1) Is there a fixed number of values you want to display, or does it vary?
2) How would you want them displayed? in a column? in a row?
Let me know the above and I'll post you the solution,
Jay
__
findlay wrote:
> I am currently having to set up a excel spreadsheet for a ski lodge business
> and there are several issues which are very confusing.
> I am using a vlookup function and it is loooking up and selecting text, but
> it is selecting the first text only because ther are several different values
> with the same value, but i would rather it display more than one answer. is
> there a variation of the vlookup function which shows two possible answers or
> is there another function which is better suited??
>
>
> "bonot1" 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. 32) Posted: Tue Aug 04, 2009 1:37 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?)
Hi,
May I request how to use vlookup or Index/Match combination to lookup the
drive utilization, for same hostname..
With Vlookup or Index/Match basic function use, I can get either the 1st
match or the last match, but not the middle one or the drive D. Any clue is
much appreciated.
Thankyou
"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 4 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
Office