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

L Gillman · External Since: Jul 28, 2009 Posts: 1

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

ck555 · External Since: Jul 28, 2009 Posts: 1

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:

a b c d
red 12 11 9
red 3 13 11

Can you help?

Jason · External Since: Jul 23, 2009 Posts: 6

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?

Ram · External Since: Jan 12, 2005 Posts: 31

Hi,
May I request how to use vlookup or Index/Match combination to lookup the
drive utilization, for same hostname..

Server1 C:\ (2) 21478666240 17488568534 3990097706 81.42297263

Server1 D:\ (3) 2.9362E+11 68383018334 2.25237E+11 23.28963077

Server1 E:\ (4) 1.25325E+11 48705869576 76619192568 38.8636309

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

Timo · External Since: Mar 23, 2006 Posts: 2

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

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

Try something this:

=SUMPRODUCT(--(E$2:E$10=A2),--(F$2:F$10=B2),G$2:G$10)

Copy down as needed.

--
Biff
Microsoft Excel MVP

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

Ray · External Since: Jun 23, 2004 Posts: 137

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 Cool

= 44. How can I achieve this result? Thanks.

Dave Peterson · External Since: Jul 08, 2005 Posts: 16047

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

= 44. How can I achieve this result? Thanks.

--

Dave Peterson

Baz · External Since: Aug 29, 2004 Posts: 10

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 Cool

= 44. How can I achieve this result? Thanks.

Abid Malik · External Since: Sep 25, 2009 Posts: 1

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 Cool

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

= 44. How can I achieve this result? Thanks.
>
> --
>
> Dave Peterson
>

Majal · External Since: Oct 15, 2009 Posts: 2

Hi Ray,

You may use the SUMIF function:

=SUMIF(A:A, "a", C:C)

Have a great day!

Majal · External Since: Oct 15, 2009 Posts: 2

Hi Ray,

Use the SUMIF function:

=SUMIF(A:A, "a", C:C)

Have a great day!

wjay29 · External Since: Oct 28, 2009 Posts: 1

Try the SUMIF function. It worked for me.

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

= 44. How can I achieve this result? Thanks.

Cstep · External Since: Oct 29, 2009 Posts: 1

Is there a way to drag this formula and not enter the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER) for every row?

This formula works great for my worksheet but I need to drag it down 10,000+
rows?

Reference formula:
=INDEX(Data!C1:C10,MATCH(1,(Data!A1:A10=A1)*(Data!B1:B10=D1),0)).

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