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

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

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