if, lookup, vlookup??

[samway301]

good morning! I have a list on a worksheet (Registration) that contains information in the table below: (there are 4 people per hole) this spreadsheet will eventually have 40 holes or so, and 400ish people.

P R S W X AB AC AG AH
[TABLE="width: 500"]
<tbody>[TR]
[TD]Hole[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[/TR]
[TR]
[TD]1A[/TD]
[TD]bob[/TD]
[TD]smith[/TD]
[TD]dog[/TD]
[TD]cat[/TD]
[TD]one[/TD]
[TD]two[/TD]
[TD]three[/TD]
[TD]four[/TD]
[/TR]
[TR]
[TD]1B[/TD]
[TD]susan[/TD]
[TD]bobby[/TD]
[TD]sign[/TD]
[TD]here[/TD]
[TD]aw[/TD]
[TD]eel[/TD]
[TD]bug[/TD]
[TD]rug[/TD]
[/TR]
</tbody>[/TABLE]

I have another worksheet called "standings sheet" that i want displayed like this:

A B C D
[TABLE="width: 500"]
<tbody>[TR]
[TD]1A[/TD]
[TD]bob smith[/TD]
[TD]1B[/TD]
[TD]susan bobby[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]dog cat[/TD]
[TD][/TD]
[TD]sign here[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]one two[/TD]
[TD][/TD]
[TD]aw eel[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]three four[/TD]
[TD][/TD]
[TD]bug rug[/TD]
[/TR]
</tbody>[/TABLE]

is there a formula out there that'll find "1A" on the registration sheet, and display the 4 names, vertically on the standings sheet? Additionally, (if possible), i'd like the standing sheet to update the names, if the holes change on the registration sheet.

 

[AlanY]

this works for your current setup

Excel 2012
	P	R	S	W	X	AB	AC	AG	AH
1	Hole	First Name	Last Name	First Name	Last Name	First Name	Last Name	First Name	Last Name
2	1A	bob	smith	dog	cat	one	two	three	four
3	1B	susan	bobby	sign	here	aw	eel	bug	rug
4	3A	C1	C2	C6	C7	C11	C12	C16	C17
5	3B	D1	D2	D6	D7	D11	D12	D16	D17
6
Registration

Excel 2012
	A	B	C	D
1	1A	bob smith	1B	susan bobby
2		dog cat		sign here
3		one two		aw eel
4		three four		bug rug
Standings

Cell Formulas
Range	Formula
B1	{=INDEX(Registration!$A:$AH,MATCH(A$1,Registration!$P:$P,0),SMALL(IF(Registration!$A$1:$AH$1="First Name",COLUMN(Registration!$A$1:$AH$1),""),ROW(A1)-ROW($A$1)+1))&" "&INDEX(Registration!$A:$AH,MATCH(A$1,Registration!$P:$P,0),SMALL(IF(Registration!$A$1:$AH$1="Last Name",COLUMN(Registration!$A$1:$AH$1),""),ROW(A1)-ROW($A$1)+1))}
D1	{=INDEX(Registration!$A:$AH,MATCH(C$1,Registration!$P:$P,0),SMALL(IF(Registration!$A$1:$AH$1="First Name",COLUMN(Registration!$A$1:$AH$1),""),ROW(C1)-ROW($A$1)+1))&" "&INDEX(Registration!$A:$AH,MATCH(C$1,Registration!$P:$P,0),SMALL(IF(Registration!$A$1:$AH$1="Last Name",COLUMN(Registration!$A$1:$AH$1),""),ROW(C1)-ROW($A$1)+1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Excel 2012
	A	B	C	D
1	3A	C1 C2	3B	D1 D2
2		C6 C7		D6 D7
3		C11 C12		D11 D12
4		C16 C17		D16 D17
Standings

 

[AlanY]

this works for your current layout

Excel 2012
	P	R	S	W	X	AB	AC	AG	AH
1	Hole	First Name	Last Name	First Name	Last Name	First Name	Last Name	First Name	Last Name
2	1A	bob	smith	dog	cat	one	two	three	four
3	1B	susan	bobby	sign	here	aw	eel	bug	rug
Registration

Excel 2012
	A	B	C	D
1	1A	bob smith	1B	susan bobby
2		dog cat		sign here
3		one two		aw eel
4		three four		bug rug
Standings

Cell Formulas
Range	Formula
B1	{=INDEX(Registration!$A:$AH,MATCH(A$1,Registration!$P:$P,0),SMALL(IF(Registration!$A$1:$AH$1="First Name",COLUMN(Registration!$A$1:$AH$1),""),ROW(A1)-ROW($A$1)+1))&" "&INDEX(Registration!$A:$AH,MATCH(A$1,Registration!$P:$P,0),SMALL(IF(Registration!$A$1:$AH$1="Last Name",COLUMN(Registration!$A$1:$AH$1),""),ROW(A1)-ROW($A$1)+1))}
D1	{=INDEX(Registration!$A:$AH,MATCH(C$1,Registration!$P:$P,0),SMALL(IF(Registration!$A$1:$AH$1="First Name",COLUMN(Registration!$A$1:$AH$1),""),ROW(C1)-ROW($A$1)+1))&" "&INDEX(Registration!$A:$AH,MATCH(C$1,Registration!$P:$P,0),SMALL(IF(Registration!$A$1:$AH$1="Last Name",COLUMN(Registration!$A$1:$AH$1),""),ROW(C1)-ROW($A$1)+1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[samway301]

IT WORKED!!!!!!!!!!!!! thank you!!!!

 

[AlanY]

you're welcome