Excel Formula Issue - Please Assist

[peachycream]

Can someone assist me with a formula issue I am having please?

I have a cell with a data validation list in it. When I select the name on that list I would like their team to populate in the cells beneath him. The team info is a different worksheet in the same workbook. I thought I could use Vlookup but that doesn't seem to work because it doesn't account for multiple entries under one name. I hope I am describing the issue accurately.

Bob		Sup	Tech
Ray		Bob	1
Tim		Bob	2
Larry		Bob	3
		Ray	4
		Ray	5
		Ray	6
		Tim	7
		Tim	8
		Tim	9
		Larry	10
		Larry	11
		Larry	12

<colgroup><col width="64" style="width: 48pt;" span="4"> <tbody> </tbody>

 

[CyrusTheVirus]

could you provide us with an example by telling us where your data validation is and where you'd like the list to be populated?

It looks like you have 4 data validation lists in column A, but then you want them all to appear in column C? I'm confused...

 

[peachycream]

could you provide us with an example by telling us where your data validation is and where you'd like the list to be populated?

It looks like you have 4 data validation lists in column A, but then you want them all to appear in column C? I'm confused...

Sorry if I was too vague.

The original 4 names are in a data validation list in cell a1 of sheet1 and the list with sups (column a) / teams (column b) are on sheet2.

I would like to select a name from the list in cell a1 from sheet1 and have the team associated with that sup on sheet2 populate cells a2,a3,a4,etc. on sheet1.

 

[63falcondude]

Something like this?

Excel 2010
	A	B	C	D	E
1	Sup	Tech			Bob
2	Bob	1			1
3	Bob	2			2
4	Bob	3			3
5	Ray	4
6	Ray	5
7	Ray	6
8	Tim	7
9	Tim	8
10	Tim	9
11	Larry	10
12	Larry	11
13	Larry	12
Sheet1

Cell Formulas
Range	Formula
E2	{=IF(ISERROR(INDEX($A$1:$B$13,SMALL(IF($A$1:$A$13=$E$1,ROW($A$1:$A$13)),ROW(1:1)),2)),"",INDEX($A$1:$B$13,SMALL(IF($A$1:$A$13=$E$1,ROW($A$1:$A$13)),ROW(1:1)),2))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Source: How To Return Multiple Match Values in Excel Using INDEX-MATCH or VLOOKUP

 

[CyrusTheVirus]

Enter with Ctrl+Shift+Enter, not just Enter. Drag down and then over.

B2 (Sheet 1):

=IFERROR(INDEX(Sheet2!A$1:A$25,SMALL(IF(Sheet2!$A$1:$A$25=$A$1,ROW(Sheet2!A$1:A$25)-ROW(Sheet2!A$1)+1),ROWS(B$1:B1))),"")

 

[peachycream]

I'm trying to understand the formula but I am confused by the last portion concerning ROWS. Should that be Sheet2! before the B$1:B1?

 

[peachycream]

Thanks everyone. I was to resolve my issues with your help.