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

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?

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

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" wrote in message
...
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" wrote in message
...
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 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?

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" wrote in message
...
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" wrote in message
...
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" wrote in
message
...
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
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?

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+18) = 44. How can I achieve this result? Thanks.

=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+18) = 44. How can I achieve this result? Thanks.

--

Dave Peterson

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+18) = 44. How can I achieve this result? Thanks.

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:C8). 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+18) = 44. How can I achieve this result? Thanks.

--

Dave Peterson

That should just be a simple sumif:
=SUMIF($A$1:$C$8,"a",$C$1:$C$8)

"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+18) = 44. How can I achieve this result? Thanks.

Hi Ray,

You may use the SUMIF function:

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

Have a great day!