Vlookup returning multiple answers

S

soph

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
 
R

Ragdyer

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

OssieMac

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
 
H

Harlan Grove

Ragdyer said:
This can be accomplished using formulas, although a Pivot Table would be
the recommended way to go. ....
Click to expand...

To me it seems an AutoFilter would be a better idea for 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.
 
S

soph

Thanks RD, this was great!

Ragdyer said:
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.
Click to expand...
 
Top