Populate a List - VLOOKUP

[sarah04]

Hi all! I am trying to populate a list in sheet 2 based on sheet one. I tried VLOOKUP and that worked fine for the first item... but this only brings the first match.

Excel Workbook
	A	B	C
9	Sheet 1
10	Team	Name	Title
11	Mayor Team	Pauline	Leadership
12	Banker Team	Daniel	Teller
13	Mayor Team	Erica	Leadership
14
15	Sheet 2
16	Mayor Team
17	here, list employees	here, list their titles
18	who are part of the
19	Mayor Team.
20
21	VLOOKUP($A$15,'Sheet 1'!$A$11:$C$12,2,FALSE)
22	the vlookup will only bring the FIRST employee listed in sheet 1 who is on the Mayor Team
23
Sheet2

Thanks!

 

[bbhgroup]

That's the way VLOOKUP works, just returns the first match. I tried to come up with something quick for this; I can do it but need some time...

If you still need this can work on later...

 

[sarah04]

Yes, I would appretiate it!

I tried using a countif with row index? but I couldn't quite get it.... it brings up a different resource's name. I can't post the formula here because it's confidential information, but here's the idea I was running with:

=IF(COUNTIF(Team,reference cell)>ROW()-ROW(cell),INDEX(ResourceName,SMALL(IF(team=reference cell,ROW(Team)-ROW(current result)+1),ROW()-ROW(reference cell)+1)),"-")

 

[danzon]

A pivot table would be the simplest and most ideal solution

 

[sarah04]

Well, I didn't think I could use the pivot tables since I'll be entering more data in the rows following the lookup, to be entered weekly. Right? I could be wrong.

 

[T. Valko]

Yes, I would appretiate it!

I tried using a countif with row index? but I couldn't quite get it.... it brings up a different resource's name. I can't post the formula here because it's confidential information, but here's the idea I was running with:

=IF(COUNTIF(Team,reference cell)>ROW()-ROW(cell),INDEX(ResourceName,SMALL(IF(team=reference cell,ROW(Team)-ROW(current result)+1),ROW()-ROW(reference cell)+1)),"-")

There is an example here:

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3

 

[danzon]

Sure you can keep adding, you will just need to refresh your pivot table source data every time you add new lines.

it would look something like this