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

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?

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

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

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

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

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