How can I lookup when match has more than one value?

bonot1 · External Since: Jul 23, 2007 Posts: 4

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?

David Hilberg · External Since: Jul 21, 2007 Posts: 8

=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?

T. Valko · External Since: Nov 24, 2006 Posts: 3426

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?

bonot1 · External Since: Jul 23, 2007 Posts: 4

This is helpful and I will think on this, but in my case nearly all of the
CountIfs will be greater than 1. To use your example, I may have 10
instances of "Joe" in my list; the lookup against "Joe" might return "abc" or
"cde". Most Joe, Pam, etc. have only one value "abc" as their match, but
some Joe, Pam, etc. have both "abc" and "cde". I need a way to know that.

"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?
>
>
>

bonot1 · External Since: Jul 23, 2007 Posts: 4

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 DeleteThis @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?
>
>
>

T. Valko · External Since: Nov 24, 2006 Posts: 3426

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.DeleteThis@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.DeleteThis@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?
>>
>>
>>

bonot1 · External Since: Jul 23, 2007 Posts: 4

Thanks. I am working with your suggestion, but I am not sure if I expressed
my problem clearly.

Using your example, in A2:A20 there would be say three different values
"abc", "cde", and "efg". When I lookup in B2:B20, there is a "123" for every
"abc", a "345" for every "cde"; VLOOKUP works fine for these. However, rows
with "efg" in column A sometimes have "789" in column B and sometimes have
"567".

What I need is to 1) be made aware that "efg" has two different matches in
column B, and 2) know what the values of those two matches are. This is what
I would like to automate.

"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 DeleteThis @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 DeleteThis @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?
> >>
> >>
> >>
>
>
>

T. Valko · External Since: Nov 24, 2006 Posts: 3426

Well, then all you need to do is test for the presence of those 2 values
that correspond to "efg". I have a feeling that your sample data is "fake"
so any formula I suggest might not work on your real data since the formula
is based on your explanation. Anyhow, try this:

=IF(COUNTIF(A$2:A$20,"efg"),IF(AND(COUNTIF(B$2:B$20,567),COUNTIF(B$2:B$20,789)),INDEX({567,789},ROWS($1:1)),IF(ROWS($1:1)=1,VLOOKUP("efg",A$2:B$20,2,0),"")),"")

Copy to a total of 2 cells.

--
Biff
Microsoft Excel MVP

"bonot1" <bonot1 RemoveThis @discussions.microsoft.com> wrote in message
news:50CD1461-B61F-40A7-A0B5-C559543D0FDB@microsoft.com...
> Thanks. I am working with your suggestion, but I am not sure if I
> expressed
> my problem clearly.
>
> Using your example, in A2:A20 there would be say three different values
> "abc", "cde", and "efg". When I lookup in B2:B20, there is a "123" for
> every
> "abc", a "345" for every "cde"; VLOOKUP works fine for these. However,
> rows
> with "efg" in column A sometimes have "789" in column B and sometimes have
> "567".
>
> What I need is to 1) be made aware that "efg" has two different matches in
> column B, and 2) know what the values of those two matches are. This is
> what
> I would like to automate.
>
> "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?
>> >>
>> >>
>> >>
>>
>>
>>

Hijosdelongi · External Since: Jul 12, 2009 Posts: 6

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....TakeThisOut@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?
>
>
>

Hijosdelongi · External Since: Jul 12, 2009 Posts: 6

Hi,

I have a Question.. is VLOOKUP plus IF possible? This is my fomula..

=VLOOKUP(IF((Data!A1:A1000=A1)*(Data!B1:B1000=D1),....

is my logical tests or conditions correct? and how will i put the VLOOKUP
codes?

Can you help me with this..

THank you so much

"T. Valko" wrote:

> Well, then all you need to do is test for the presence of those 2 values
> that correspond to "efg". I have a feeling that your sample data is "fake"
> so any formula I suggest might not work on your real data since the formula
> is based on your explanation. Anyhow, try this:
>
> =IF(COUNTIF(A$2:A$20,"efg"),IF(AND(COUNTIF(B$2:B$20,567),COUNTIF(B$2:B$20,789)),INDEX({567,789},ROWS($1:1)),IF(ROWS($1:1)=1,VLOOKUP("efg",A$2:B$20,2,0),"")),"")
>
> Copy to a total of 2 cells.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "bonot1" <bonot1.TakeThisOut@discussions.microsoft.com> wrote in message
> news:50CD1461-B61F-40A7-A0B5-C559543D0FDB@microsoft.com...
> > Thanks. I am working with your suggestion, but I am not sure if I
> > expressed
> > my problem clearly.
> >
> > Using your example, in A2:A20 there would be say three different values
> > "abc", "cde", and "efg". When I lookup in B2:B20, there is a "123" for
> > every
> > "abc", a "345" for every "cde"; VLOOKUP works fine for these. However,
> > rows
> > with "efg" in column A sometimes have "789" in column B and sometimes have
> > "567".
> >
> > What I need is to 1) be made aware that "efg" has two different matches in
> > column B, and 2) know what the values of those two matches are. This is
> > what
> > I would like to automate.
> >
> > "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.TakeThisOut@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.TakeThisOut@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?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

T. Valko · External Since: Nov 24, 2006 Posts: 3426

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?
>>
>>
>>

Hijosdelongi · External Since: Jul 12, 2009 Posts: 6

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?
> >>
> >>
> >>
>
>
>

T. Valko · External Since: Nov 24, 2006 Posts: 3426

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?
>> >>
>> >>
>> >>
>>
>>
>>

Hijosdelongi · External Since: Jul 12, 2009 Posts: 6

Thank you so much! but i want to ask, what does 1 and 0 represents? and im a
little confused, why is it C1:C5?

Thanks 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 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?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Hijosdelongi · External Since: Jul 12, 2009 Posts: 6

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?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Hijosdelongi · External Since: Jul 12, 2009 Posts: 6

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 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?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

T. Valko · External Since: Nov 24, 2006 Posts: 3426

>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.

(Bob=Sue)*(x=a) = 0
(Sue=Sue)*(x=a) = 0
(Bob=Sue)*(z=a) = 0
(Sue=Sue)*(a=a) = 1
(Tom=Sue)*(h=a) = 0

This array of 1s and 0s make up the lookup_array.

MATCH(1,{0;0;0;1;0},0)

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 DeleteThis @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 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?
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

T. Valko · External Since: Nov 24, 2006 Posts: 3426

Yes, but you don't need to repeat the sheet name.

=INDEX(Data!C1:C5,MATCH(1,(Data!A1:A5=E1)*(Data!B1:B5=F1)*(????????????),0))

--
Biff
Microsoft Excel MVP

"Hijosdelongi" <Hijosdelongi RemoveThis @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 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?
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

findlay · External Since: Jul 23, 2009 Posts: 1

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?

AMLL · External Since: Jul 27, 2009 Posts: 1

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?