Hi

I have 2 questions:

1. Can I use a vlookup to return all of the results rather than just one at
a time? I need to find all of the staff associated with a particular manager
out of a list.
2. Team sizes can vary so in order to return all of the staff member's names
I need to first manually count how many staff are in the team. Can I use a
formula to return all results for a lookup regardless of the number or do I
need to use a macro to do this?

Cheers
Soph

This can be accomplished using formulas, although a Pivot Table would be the
recommended way to go.

Debra Dalgleish has a web site with an extensive number of pages dedicated
to Pivot tables.

http://www.contextures.com/tiptech.html

Scroll down to the P's, and at the end, there's a page with an intro to
Pivots.

If you still would want a formula, post back with a description of your
datalist configuration.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"soph" wrote in message
...
Hi

I have 2 questions:

1. Can I use a vlookup to return all of the results rather than just one
at
a time? I need to find all of the staff associated with a particular
manager
out of a list.
2. Team sizes can vary so in order to return all of the staff member's
names
I need to first manually count how many staff are in the team. Can I use
a
formula to return all results for a lookup regardless of the number or do
I
need to use a macro to do this?

Cheers
Soph

Hi Soph,

Have you considered using AutoFilter. It is simply a magic way of
associating data like you are referring to. When using AutoFilter you can
also use subtotal function to perform a number of mathematical calculations
like count, sum, average etc. on the visible cells.

A tip when using AutoFilter is to leave 3 or 4 blank lines above the column
headers and then click on the first cell below the column headers in column A
and Freeze Panes. The headers and calculations then always remain visible
irrespective of the scrolling on the screen.

If you have not used the above before then look them up in help. They are
quite simple to use but if you have any problems then reply with your
questions and I'll attempt to answer them.

Regards,

OssieMac



"soph" wrote:

Hi

I have 2 questions:

1. Can I use a vlookup to return all of the results rather than just one at
a time? I need to find all of the staff associated with a particular manager
out of a list.
2. Team sizes can vary so in order to return all of the staff member's names
I need to first manually count how many staff are in the team. Can I use a
formula to return all results for a lookup regardless of the number or do I
need to use a macro to do this?

Cheers
Soph

Here's a small sample file that demonstrates 2 different formula methods.

Sample file - lookup with multiple lookup values


http://cjoint.com/?ivesCzGGpk

--
Biff
Microsoft Excel MVP


"soph" wrote in message
...
Hi

I have 2 questions:

1. Can I use a vlookup to return all of the results rather than just one
at
a time? I need to find all of the staff associated with a particular
manager
out of a list.
2. Team sizes can vary so in order to return all of the staff member's
names
I need to first manually count how many staff are in the team. Can I use
a
formula to return all results for a lookup regardless of the number or do
I
need to use a macro to do this?

Cheers
Soph

"Ragdyer" wrote...
This can be accomplished using formulas, although a Pivot Table would be
the recommended way to go.
....
"soph" wrote...
....
1. Can I use a vlookup to return all of the results rather than just one
at a time? I need to find all of the staff associated with a particular
manager out of a list.

To me it seems an AutoFilter would be a better idea for this.

2. Team sizes can vary so in order to return all of the staff member's
names I need to first manually count how many staff are in the team.
Can I use a formula to return all results for a lookup regardless of the
number or do I need to use a macro to do this?

One way. If your managers and employees are in a mutliple row, 2-column
range, name the column with managers Mgr and the column with employees Emp.
Then you could create team lists starting in cell D2 using these formulas.

D2:
=T(Mgr)

E2:
=COUNTIF(Mgr,D2)

F2:
=INDEX(Emp,MATCH($D2,Mgr,0))

G2 [array formula]:
=IF(COLUMNS($F2:G2)<=$E2,INDEX(Emp,
SMALL(IF(Mgr=$D2,ROW(Mgr)-MIN(ROW(Mgr))+1),COLUMNS($F2:G2))),"")

Fill G2 right as far as needed. I'll assume that's to column K.

D3 [array formula]:
=INDEX(Mgr,MATCH(0,COUNTIF(D$2:D2,Mgr),0))

Fill E2:K2 down into E3:K3, then fill D3:K3 down as far as needed.

Thanks RD, this was great!

"Ragdyer" wrote:

This can be accomplished using formulas, although a Pivot Table would be the
recommended way to go.

Debra Dalgleish has a web site with an extensive number of pages dedicated
to Pivot tables.

http://www.contextures.com/tiptech.html

Scroll down to the P's, and at the end, there's a page with an intro to
Pivots.

If you still would want a formula, post back with a description of your
datalist configuration.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"soph" wrote in message
...
Hi

I have 2 questions:

1. Can I use a vlookup to return all of the results rather than just one
at
a time? I need to find all of the staff associated with a particular
manager
out of a list.
2. Team sizes can vary so in order to return all of the staff member's
names
I need to first manually count how many staff are in the team. Can I use
a
formula to return all results for a lookup regardless of the number or do
I
need to use a macro to do this?

Cheers
Soph

Debra deserves the thanks.

Appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"soph" wrote in message
...
Thanks RD, this was great!

"Ragdyer" wrote:

This can be accomplished using formulas, although a Pivot Table would be
the
recommended way to go.

Debra Dalgleish has a web site with an extensive number of pages
dedicated
to Pivot tables.

http://www.contextures.com/tiptech.html

Scroll down to the P's, and at the end, there's a page with an intro to
Pivots.

If you still would want a formula, post back with a description of your
datalist configuration.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"soph" wrote in message
...
Hi

I have 2 questions:

1. Can I use a vlookup to return all of the results rather than just
one
at
a time? I need to find all of the staff associated with a particular
manager
out of a list.
2. Team sizes can vary so in order to return all of the staff member's
names
I need to first manually count how many staff are in the team. Can I
use
a
formula to return all results for a lookup regardless of the number or
do
I
need to use a macro to do this?

Cheers
Soph