Thank you for your support.

Can someone please help me with the following array? type of question.

What formula can I use to return a list of results when there is more than
one result?
If I use VLOOKup it returns only the first instance of the result.


For example: Looking up cell a1 value of 22 should result in the following
list:
Dog
Biscuit
Steak

A B C D

22 34 Apple
33 34 Fish
16 22 Dog
91 1 Orange
15 3 Tangerine
14 22 Biscuit
21 1 Tea
34 5 Salmon
17 22 Steak
7 Herring
8 Cod
1 Orange
1 Castle

You can set up another column to give you a unique reference. For
example, put this formula in E1:

=IF(C1="","",C1&"_"&COUNTIF(C$1:C1,C1))

then copy this down to cover the values in column C.

Then, with 22 in A1, you can put this formula in B1:

=IF(ISNA(MATCH(A$1&"_"&ROW(A1),E:E,0)),"",INDEX(D: D,MATCH(A$1&"_"&ROW
(A1),E:E,0)))

Then copy this down as far as you think you might need it (i.e. to get
all the duplicates).

Hope this helps.

Pete

On Jan 2, 8:10*pm, ORLANDO VAZQUEZ
wrote:
Thank you for your support.

Can someone please help me with the following array? type of question.

What formula can I use to return a list of results when there is more than
one result?
If I use VLOOKup it returns only the first instance of the result.

For example: *Looking up cell a1 value of 22 should result in the following
list:
Dog *
Biscuit *
Steak

A * * * B * * * C * * * D

22 * * * * * * *34 * * *Apple
33 * * * * * * *34 * * *Fish
16 * * * * * * *22 * * *Dog
91 * * * * * * *1 * * * Orange
15 * * * * * * *3 * * * Tangerine
14 * * * * * * *22 * * *Biscuit
21 * * * * * * *1 * * * Tea
34 * * * * * * *5 * * * Salmon
17 * * * * * * *22 * * *Steak
* * * * * * * * 7 * * * Herring
* * * * * * * * 8 * * * Cod
* * * * * * * * 1 * * * Orange
* * * * * * * * 1 * * * Castle

Hi,

Try this. Drag down to find multiple results. It produce NUM errors when it
doesn't find a resuly and you can wrap the whole thing in a n error trap to
elminate this.

=INDEX($D$1:$D$10,SMALL(IF(($C$1:$C$10=$A$1),ROW($ C$1:$C$10)),ROW()))

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array


Mike

"ORLANDO VAZQUEZ" wrote:


Thank you for your support.

Can someone please help me with the following array? type of question.

What formula can I use to return a list of results when there is more than
one result?
If I use VLOOKup it returns only the first instance of the result.


For example: Looking up cell a1 value of 22 should result in the following
list:
Dog
Biscuit
Steak

A B C D

22 34 Apple
33 34 Fish
16 22 Dog
91 1 Orange
15 3 Tangerine
14 22 Biscuit
21 1 Tea
34 5 Salmon
17 22 Steak
7 Herring
8 Cod
1 Orange
1 Castle

This is very good. Thank you.

"ORLANDO VAZQUEZ" wrote:


Thank you for your support.

Can someone please help me with the following array? type of question.

What formula can I use to return a list of results when there is more than
one result?
If I use VLOOKup it returns only the first instance of the result.


For example: Looking up cell a1 value of 22 should result in the following
list:
Dog
Biscuit
Steak

A B C D

22 34 Apple
33 34 Fish
16 22 Dog
91 1 Orange
15 3 Tangerine
14 22 Biscuit
21 1 Tea
34 5 Salmon
17 22 Steak
7 Herring
8 Cod
1 Orange
1 Castle

This worked fine. Thank you !

"ORLANDO VAZQUEZ" wrote:


Thank you for your support.

Can someone please help me with the following array? type of question.

What formula can I use to return a list of results when there is more than
one result?
If I use VLOOKup it returns only the first instance of the result.


For example: Looking up cell a1 value of 22 should result in the following
list:
Dog
Biscuit
Steak

A B C D

22 34 Apple
33 34 Fish
16 22 Dog
91 1 Orange
15 3 Tangerine
14 22 Biscuit
21 1 Tea
34 5 Salmon
17 22 Steak
7 Herring
8 Cod
1 Orange
1 Castle

Try this array formula** entered in cell E1:

=IF(ROWS(E$1:E1)<=COUNTIF(B$1:B$20,A$1),INDEX(C$1: C$20,SMALL(IF(B$1:B$20=A$1,ROW(C$1:C$20)),ROWS(E$1 :E1))-MIN(ROW(C$1:C$20))+1),"")

Copy down until you get blanks meaning all the relative data has been
extracted.

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


"ORLANDO VAZQUEZ" wrote in message
...

Thank you for your support.

Can someone please help me with the following array? type of question.

What formula can I use to return a list of results when there is more than
one result?
If I use VLOOKup it returns only the first instance of the result.


For example: Looking up cell a1 value of 22 should result in the
following
list:
Dog
Biscuit
Steak

A B C D

22 34 Apple
33 34 Fish
16 22 Dog
91 1 Orange
15 3 Tangerine
14 22 Biscuit
21 1 Tea
34 5 Salmon
17 22 Steak
7 Herring
8 Cod
1 Orange
1 Castle

For some reason this returns a circular reference....

"T. Valko" wrote:

Try this array formula** entered in cell E1:

=IF(ROWS(E$1:E1)<=COUNTIF(B$1:B$20,A$1),INDEX(C$1: C$20,SMALL(IF(B$1:B$20=A$1,ROW(C$1:C$20)),ROWS(E$1 :E1))-MIN(ROW(C$1:C$20))+1),"")

Copy down until you get blanks meaning all the relative data has been
extracted.

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


"ORLANDO VAZQUEZ" wrote in message
...

Thank you for your support.

Can someone please help me with the following array? type of question.

What formula can I use to return a list of results when there is more than
one result?
If I use VLOOKup it returns only the first instance of the result.


For example: Looking up cell a1 value of 22 should result in the
following
list:
Dog
Biscuit
Steak

A B C D

22 34 Apple
33 34 Fish
16 22 Dog
91 1 Orange
15 3 Tangerine
14 22 Biscuit
21 1 Tea
34 5 Salmon
17 22 Steak
7 Herring
8 Cod
1 Orange
1 Castle

This worked great. Thank you!


"Mike H" wrote:

Hi,

Try this. Drag down to find multiple results. It produce NUM errors when it
doesn't find a resuly and you can wrap the whole thing in a n error trap to
elminate this.

=INDEX($D$1:$D$10,SMALL(IF(($C$1:$C$10=$A$1),ROW($ C$1:$C$10)),ROW()))

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array


Mike

"ORLANDO VAZQUEZ" wrote:


Thank you for your support.

Can someone please help me with the following array? type of question.

What formula can I use to return a list of results when there is more than
one result?
If I use VLOOKup it returns only the first instance of the result.


For example: Looking up cell a1 value of 22 should result in the following
list:
Dog
Biscuit
Steak

A B C D

22 34 Apple
33 34 Fish
16 22 Dog
91 1 Orange
15 3 Tangerine
14 22 Biscuit
21 1 Tea
34 5 Salmon
17 22 Steak
7 Herring
8 Cod
1 Orange
1 Castle

Mike,

Thank you. This works good.

"Mike H" wrote:

Hi,

Try this. Drag down to find multiple results. It produce NUM errors when it
doesn't find a resuly and you can wrap the whole thing in a n error trap to
elminate this.

=INDEX($D$1:$D$10,SMALL(IF(($C$1:$C$10=$A$1),ROW($ C$1:$C$10)),ROW()))

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array


Mike

"ORLANDO VAZQUEZ" wrote:


Thank you for your support.

Can someone please help me with the following array? type of question.

What formula can I use to return a list of results when there is more than
one result?
If I use VLOOKup it returns only the first instance of the result.


For example: Looking up cell a1 value of 22 should result in the following
list:
Dog
Biscuit
Steak

A B C D

22 34 Apple
33 34 Fish
16 22 Dog
91 1 Orange
15 3 Tangerine
14 22 Biscuit
21 1 Tea
34 5 Salmon
17 22 Steak
7 Herring
8 Cod
1 Orange
1 Castle

This works very good. Thank you.


"Pete_UK" wrote:

You can set up another column to give you a unique reference. For
example, put this formula in E1:

=IF(C1="","",C1&"_"&COUNTIF(C$1:C1,C1))

then copy this down to cover the values in column C.

Then, with 22 in A1, you can put this formula in B1:

=IF(ISNA(MATCH(A$1&"_"&ROW(A1),E:E,0)),"",INDEX(D: D,MATCH(A$1&"_"&ROW
(A1),E:E,0)))

Then copy this down as far as you think you might need it (i.e. to get
all the duplicates).

Hope this helps.

Pete

On Jan 2, 8:10 pm, ORLANDO VAZQUEZ
wrote:
Thank you for your support.

Can someone please help me with the following array? type of question.

What formula can I use to return a list of results when there is more than
one result?
If I use VLOOKup it returns only the first instance of the result.

For example: Looking up cell a1 value of 22 should result in the following
list:
Dog
Biscuit
Steak

A B C D

22 34 Apple
33 34 Fish
16 22 Dog
91 1 Orange
15 3 Tangerine
14 22 Biscuit
21 1 Tea
34 5 Salmon
17 22 Steak
7 Herring
8 Cod
1 Orange
1 Castle

For some reason this returns a circular reference....

Where did you enter the formula? You must have entered it within one of the
referenced ranges. If your data really is where you said it was and you
enter the formula in E1 as I suggested there is no way you'll get a circular
reference. You can enter the formula anywhere *except* within the range
B1:C20.

While the other suggestions will work, this version is the most robust.


--
Biff
Microsoft Excel MVP


"ORLANDO VAZQUEZ" wrote in message
...
For some reason this returns a circular reference....

"T. Valko" wrote:

Try this array formula** entered in cell E1:

=IF(ROWS(E$1:E1)<=COUNTIF(B$1:B$20,A$1),INDEX(C$1: C$20,SMALL(IF(B$1:B$20=A$1,ROW(C$1:C$20)),ROWS(E$1 :E1))-MIN(ROW(C$1:C$20))+1),"")

Copy down until you get blanks meaning all the relative data has been
extracted.

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


"ORLANDO VAZQUEZ" wrote in message
...

Thank you for your support.

Can someone please help me with the following array? type of question.

What formula can I use to return a list of results when there is more
than
one result?
If I use VLOOKup it returns only the first instance of the result.


For example: Looking up cell a1 value of 22 should result in the
following
list:
Dog
Biscuit
Steak

A B C D

22 34 Apple
33 34 Fish
16 22 Dog
91 1 Orange
15 3 Tangerine
14 22 Biscuit
21 1 Tea
34 5 Salmon
17 22 Steak
7 Herring
8 Cod
1 Orange
1 Castle

I moved my data to correspond to the formula and it works great. Thank you!


"ORLANDO VAZQUEZ" wrote:


Thank you for your support.

Can someone please help me with the following array? type of question.

What formula can I use to return a list of results when there is more than
one result?
If I use VLOOKup it returns only the first instance of the result.


For example: Looking up cell a1 value of 22 should result in the following
list:
Dog
Biscuit
Steak

A B C D

22 34 Apple
33 34 Fish
16 22 Dog
91 1 Orange
15 3 Tangerine
14 22 Biscuit
21 1 Tea
34 5 Salmon
17 22 Steak
7 Herring
8 Cod
1 Orange
1 Castle

Mike,

I want to transpose the results so that all appear in row 1 rather than
stacked in column E. Can you help me ? I tried but cannot figure it out.
The reason is that I want each corresponding set of results to appear on the
line it corresponds to.


"Mike H" wrote:

Hi,

Try this. Drag down to find multiple results. It produce NUM errors when it
doesn't find a resuly and you can wrap the whole thing in a n error trap to
elminate this.

=INDEX($D$1:$D$10,SMALL(IF(($C$1:$C$10=$A$1),ROW($ C$1:$C$10)),ROW()))

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array


Mike

"ORLANDO VAZQUEZ" wrote:


Thank you for your support.

Can someone please help me with the following array? type of question.

What formula can I use to return a list of results when there is more than
one result?
If I use VLOOKup it returns only the first instance of the result.


For example: Looking up cell a1 value of 22 should result in the following
list:
Dog
Biscuit
Steak

A B C D

22 34 Apple
33 34 Fish
16 22 Dog
91 1 Orange
15 3 Tangerine
14 22 Biscuit
21 1 Tea
34 5 Salmon
17 22 Steak
7 Herring
8 Cod
1 Orange
1 Castle

Thanks for feeding back!

You don't want to move data to accommodate a formula, you want to be able to
write the formula to accommodate the data.

That's why it's good idea to tell us where your data *really is located* and
where you want the results to appear. Very few people do this, though!!!

--
Biff
Microsoft Excel MVP


"ORLANDO VAZQUEZ" wrote in message
...
I moved my data to correspond to the formula and it works great. Thank
you!


"ORLANDO VAZQUEZ" wrote:


Thank you for your support.

Can someone please help me with the following array? type of question.

What formula can I use to return a list of results when there is more
than
one result?
If I use VLOOKup it returns only the first instance of the result.


For example: Looking up cell a1 value of 22 should result in the
following
list:
Dog
Biscuit
Steak

A B C D

22 34 Apple
33 34 Fish
16 22 Dog
91 1 Orange
15 3 Tangerine
14 22 Biscuit
21 1 Tea
34 5 Salmon
17 22 Steak
7 Herring
8 Cod
1 Orange
1 Castle

Pete,
Can this be modified so the results appear in row 1 horizontally left to
right rather than in column e vertically ? And can I then copy that formula
down to apply to next row?

"ORLANDO VAZQUEZ" wrote:


Thank you for your support.

Can someone please help me with the following array? type of question.

What formula can I use to return a list of results when there is more than
one result?
If I use VLOOKup it returns only the first instance of the result.


For example: Looking up cell a1 value of 22 should result in the following
list:
Dog
Biscuit
Steak

A B C D

22 34 Apple
33 34 Fish
16 22 Dog
91 1 Orange
15 3 Tangerine
14 22 Biscuit
21 1 Tea
34 5 Salmon
17 22 Steak
7 Herring
8 Cod
1 Orange
1 Castle

Try this array formula entered in E1:

=IF(COLUMNS($E1:E1)<=COUNTIF($B$1:$B$20,$A1),INDEX ($C$1:$C$20,SMALL(IF($B$1:$B$20=$A1,ROW(C$1:C$20)) ,COLUMNS($E1:E1))-MIN(ROW(C$1:C$20))+1),"")

Copy down as needed then copy across until you get a *full column* of blanks
meaning all data has been extracted.

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


"ORLANDO VAZQUEZ" wrote in message
...
Mike,

I want to transpose the results so that all appear in row 1 rather than
stacked in column E. Can you help me ? I tried but cannot figure it out.
The reason is that I want each corresponding set of results to appear on
the
line it corresponds to.


"Mike H" wrote:

Hi,

Try this. Drag down to find multiple results. It produce NUM errors when
it
doesn't find a resuly and you can wrap the whole thing in a n error trap
to
elminate this.

=INDEX($D$1:$D$10,SMALL(IF(($C$1:$C$10=$A$1),ROW($ C$1:$C$10)),ROW()))

'This is an array formula which must be entered with CTRL+Shift+Enter and
NOT
'just enter. If you do it correctly then Excel will put curly brackets
around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array


Mike

"ORLANDO VAZQUEZ" wrote:


Thank you for your support.

Can someone please help me with the following array? type of question.

What formula can I use to return a list of results when there is more
than
one result?
If I use VLOOKup it returns only the first instance of the result.


For example: Looking up cell a1 value of 22 should result in the
following
list:
Dog
Biscuit
Steak

A B C D

22 34 Apple
33 34 Fish
16 22 Dog
91 1 Orange
15 3 Tangerine
14 22 Biscuit
21 1 Tea
34 5 Salmon
17 22 Steak
7 Herring
8 Cod
1 Orange
1 Castle

Can this formula be modified so that the results appear in rows rather than
the one column?
Reason is I want to be able to copy the formula down to each row to apply to
each row and results sprawling out to right.



"T. Valko" wrote:

Thanks for feeding back!

You don't want to move data to accommodate a formula, you want to be able to
write the formula to accommodate the data.

That's why it's good idea to tell us where your data *really is located* and
where you want the results to appear. Very few people do this, though!!!

--
Biff
Microsoft Excel MVP


"ORLANDO VAZQUEZ" wrote in message
...
I moved my data to correspond to the formula and it works great. Thank
you!


"ORLANDO VAZQUEZ" wrote:


Thank you for your support.

Can someone please help me with the following array? type of question.

What formula can I use to return a list of results when there is more
than
one result?
If I use VLOOKup it returns only the first instance of the result.


For example: Looking up cell a1 value of 22 should result in the
following
list:
Dog
Biscuit
Steak

A B C D

22 34 Apple
33 34 Fish
16 22 Dog
91 1 Orange
15 3 Tangerine
14 22 Biscuit
21 1 Tea
34 5 Salmon
17 22 Steak
7 Herring
8 Cod
1 Orange
1 Castle

Fantastic !


"T. Valko" wrote:

Try this array formula entered in E1:

=IF(COLUMNS($E1:E1)<=COUNTIF($B$1:$B$20,$A1),INDEX ($C$1:$C$20,SMALL(IF($B$1:$B$20=$A1,ROW(C$1:C$20)) ,COLUMNS($E1:E1))-MIN(ROW(C$1:C$20))+1),"")

Copy down as needed then copy across until you get a *full column* of blanks
meaning all data has been extracted.

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


"ORLANDO VAZQUEZ" wrote in message
...
Mike,

I want to transpose the results so that all appear in row 1 rather than
stacked in column E. Can you help me ? I tried but cannot figure it out.
The reason is that I want each corresponding set of results to appear on
the
line it corresponds to.


"Mike H" wrote:

Hi,

Try this. Drag down to find multiple results. It produce NUM errors when
it
doesn't find a resuly and you can wrap the whole thing in a n error trap
to
elminate this.

=INDEX($D$1:$D$10,SMALL(IF(($C$1:$C$10=$A$1),ROW($ C$1:$C$10)),ROW()))

'This is an array formula which must be entered with CTRL+Shift+Enter and
NOT
'just enter. If you do it correctly then Excel will put curly brackets
around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array


Mike

"ORLANDO VAZQUEZ" wrote:


Thank you for your support.

Can someone please help me with the following array? type of question.

What formula can I use to return a list of results when there is more
than
one result?
If I use VLOOKup it returns only the first instance of the result.


For example: Looking up cell a1 value of 22 should result in the
following
list:
Dog
Biscuit
Steak

A B C D

22 34 Apple
33 34 Fish
16 22 Dog
91 1 Orange
15 3 Tangerine
14 22 Biscuit
21 1 Tea
34 5 Salmon
17 22 Steak
7 Herring
8 Cod
1 Orange
1 Castle

Yes, see my reply in the other branch of this thread.

--
Biff
Microsoft Excel MVP


"ORLANDO VAZQUEZ" wrote in message
...
Can this formula be modified so that the results appear in rows rather
than
the one column?
Reason is I want to be able to copy the formula down to each row to apply
to
each row and results sprawling out to right.



"T. Valko" wrote:

Thanks for feeding back!

You don't want to move data to accommodate a formula, you want to be able
to
write the formula to accommodate the data.

That's why it's good idea to tell us where your data *really is located*
and
where you want the results to appear. Very few people do this, though!!!

--
Biff
Microsoft Excel MVP


"ORLANDO VAZQUEZ" wrote in message
...
I moved my data to correspond to the formula and it works great. Thank
you!


"ORLANDO VAZQUEZ" wrote:


Thank you for your support.

Can someone please help me with the following array? type of question.

What formula can I use to return a list of results when there is more
than
one result?
If I use VLOOKup it returns only the first instance of the result.


For example: Looking up cell a1 value of 22 should result in the
following
list:
Dog
Biscuit
Steak

A B C D

22 34 Apple
33 34 Fish
16 22 Dog
91 1 Orange
15 3 Tangerine
14 22 Biscuit
21 1 Tea
34 5 Salmon
17 22 Steak
7 Herring
8 Cod
1 Orange
1 Castle

You're welcome - I see you've also had other help.

Pete

On Jan 2, 9:21*pm, ORLANDO VAZQUEZ
wrote:
This works very good. *Thank you.

Please tell me exactly which cells you are using.

Pete

On Jan 2, 10:17*pm, ORLANDO VAZQUEZ
wrote:
Pete,
Can this be modified so the results appear in row 1 horizontally left to
right rather than in column e vertically ? *And can I then copy that formula
down to apply to next row?



"ORLANDO VAZQUEZ" wrote:

Thank you for your support.

Can someone please help me with the following array? type of question.

What formula can I use to return a list of results when there is more than
one result?
If I use VLOOKup it returns only the first instance of the result.

For example: *Looking up cell a1 value of 22 should result in the following
list:
Dog *
Biscuit *
Steak

A *B * * * C * * * D

22 * * * * 34 * * *Apple
33 * * * * 34 * * *Fish
16 * * * * 22 * * *Dog
91 * * * * 1 * * * Orange
15 * * * * 3 * * * Tangerine
14 * * * * 22 * * *Biscuit
21 * * * * 1 * * * Tea
34 * * * * 5 * * * Salmon
17 * * * * 22 * * *Steak
* * * * * *7 * * * Herring
* * * * * *8 * * * Cod
* * * * * *1 * * * Orange
* * * * * *1 * * * Castle- Hide quoted text -

- Show quoted text -

That sounds like a satisfied customer!

Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"ORLANDO VAZQUEZ" wrote in message
...
Fantastic !


"T. Valko" wrote:

Try this array formula entered in E1:

=IF(COLUMNS($E1:E1)<=COUNTIF($B$1:$B$20,$A1),INDEX ($C$1:$C$20,SMALL(IF($B$1:$B$20=$A1,ROW(C$1:C$20)) ,COLUMNS($E1:E1))-MIN(ROW(C$1:C$20))+1),"")

Copy down as needed then copy across until you get a *full column* of
blanks
meaning all data has been extracted.

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


"ORLANDO VAZQUEZ" wrote in message
...
Mike,

I want to transpose the results so that all appear in row 1 rather than
stacked in column E. Can you help me ? I tried but cannot figure it
out.
The reason is that I want each corresponding set of results to appear
on
the
line it corresponds to.


"Mike H" wrote:

Hi,

Try this. Drag down to find multiple results. It produce NUM errors
when
it
doesn't find a resuly and you can wrap the whole thing in a n error
trap
to
elminate this.

=INDEX($D$1:$D$10,SMALL(IF(($C$1:$C$10=$A$1),ROW($ C$1:$C$10)),ROW()))

'This is an array formula which must be entered with CTRL+Shift+Enter
and
NOT
'just enter. If you do it correctly then Excel will put curly brackets
around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array


Mike

"ORLANDO VAZQUEZ" wrote:


Thank you for your support.

Can someone please help me with the following array? type of
question.

What formula can I use to return a list of results when there is
more
than
one result?
If I use VLOOKup it returns only the first instance of the result.


For example: Looking up cell a1 value of 22 should result in the
following
list:
Dog
Biscuit
Steak

A B C D

22 34 Apple
33 34 Fish
16 22 Dog
91 1 Orange
15 3 Tangerine
14 22 Biscuit
21 1 Tea
34 5 Salmon
17 22 Steak
7 Herring
8 Cod
1 Orange
1 Castle

Pete,
I have tried the match row formula, and I'm getting no return data. I'm not
getting any error message, so I know the array formula is correctly entered.
The rows i'm trying to match definitely have matches, so I'm not sure what is
wrong.

On the original example from Orlando, there wasn't any data in Column E, I'm
not sure why the formula references it, but followed as a blank column.

Any help would be greatly appreciated!

Array Formula (entered in Column G):
{=IF(ISNA(MATCH(F$4&"_"&ROW(F4),H:H)),"",INDEX(C:C ,MATCH(F$4&"_"&ROW(F4),H:H,0)))}



MATCH B RETURN MULTIPLES C LOOKUP DATA F
COL A COL B COL C COL D COL E COL F
JOBNO ACCOUNTNO TECH blank CMPLDATE ACCOUNTNO
103340 8383600080022459 4021 2/23/2010 8383600230075738
103912 8383600270464099 4179 8383600270083444
104372 8383600270462044 4066 8383600270106310
104989 8383600150028501 4062 8383600270462085
105181 8383600230075738 4080 8383600270464099
105357 8383600280656866 4181 8383600400133820
105560 8383600270460592 4033 8383601090023123
105658 8383600270083444 4248 8383600410118803
106070 8383600280633113 4196 8383600130036087
106335 8383600280621159 4143 8383600070024275
106675 8383600420002492 4187 8383600280499291
108070 8383600230075738 4171
109721 8383600410101080 4248
110796 8383600230066257 4063
111383 8383600270083444 4038
111698 8383600310130601 4089
111813 8383600280663763 4027
112417 8383600270464099 4143

Thanks,
Lynn








"Pete_UK" wrote:

You can set up another column to give you a unique reference. For
example, put this formula in E1:

=IF(C1="","",C1&"_"&COUNTIF(C$1:C1,C1))

then copy this down to cover the values in column C.

Then, with 22 in A1, you can put this formula in B1:

=IF(ISNA(MATCH(A$1&"_"&ROW(A1),E:E,0)),"",INDEX(D: D,MATCH(A$1&"_"&ROW
(A1),E:E,0)))

Then copy this down as far as you think you might need it (i.e. to get
all the duplicates).

Hope this helps.

Pete

On Jan 2, 8:10 pm, ORLANDO VAZQUEZ
wrote:
Thank you for your support.

Can someone please help me with the following array? type of question.

What formula can I use to return a list of results when there is more than
one result?
If I use VLOOKup it returns only the first instance of the result.

For example: Looking up cell a1 value of 22 should result in the following
list:
Dog
Biscuit
Steak

A B C D

22 34 Apple
33 34 Fish
16 22 Dog
91 1 Orange
15 3 Tangerine
14 22 Biscuit
21 1 Tea
34 5 Salmon
17 22 Steak
7 Herring
8 Cod
1 Orange
1 Castle