Lookup to populate names with groups

[sparky2205]

Hi folks,
I have data in this format:

Group1	Group2	Group3	Group4	Group5	Group6	Group7	Group8	Group9	Group10
User1	User2	User1	User3	User1	User5	User4	User7	User8	User6
User5	User5	User7	User4	User2	User6		User10	User1	User8
User8	User4	User9	User6	User3	User7				User9
	User10			User4	User3
				User5	User1
				User6
				User7

I want to display the data in this format:

	Group1	Group2	Group3	Group4	Group5	Group6	Group7	Group8	Group9	Group10
User1	User1		User1		User1				User1
User2		User2			User2
User3				User3	User3
User4		User4		User4	User4		User4
User5	User5	User5			User5	User5				User6
User6			User7	User6	User6	User6
User7					User7	User7		User7		User8
User8	User8					User3			User8	User9
User9			User9			User1
User10		User10						User10

I've tried a few lookups but I'm just not getting it right.
Any help is much appreciated.

 

[Dan_W]

Just checking, but I'm assuming/hoping that the cells in red are mistakes, because otherwise I'm a little lost....

MrExcel
	A	B	C	D	E	F	G	H	I	J	K
1		Group1	Group2	Group3	Group4	Group5	Group6	Group7	Group8	Group9	Group10
2	User1	User1		User1		User1				User1
3	User2		User2			User2
4	User3				User3	User3
5	User4		User4		User4	User4		User4
6	User5	User5	User5			User5	User5				User6
7	User6			User7	User6	User6	User6
8	User7					User7	User7		User7		User8
9	User8	User8					User3			User8	User9
10	User9			User9			User1
11	User10		User10						User10
Sheet3

 

[Skovgaard]

Agree with Dan_W, the red cells should be a mistake...


Below formula should do the trick.

=IFERROR(INDEX(A$1:A$8,MATCH($A12;A$1:A$8,0),0),"")

/Skovgaard

 

[sparky2205]

Oops! Silly me. You are correct.

	Group1	Group2	Group3	Group4	Group5	Group6	Group7	Group8	Group9	Group10
User1	User1		User1		User1	User1			User1
User2		User2			User2
User3				User3	User3	User3
User4		User4		User4	User4		User4
User5	User5	User5			User5	User5
User6			User6	User6	User6	User6				User6
User7					User7	User7		User7
User8	User8								User8	User8
User9			User9							User9
User10		User10						User10

 

[sparky2205]

Thanks Skovgaard,
that works perfectly. Just one correction in case someone else is ever looking at this:
=IFERROR(INDEX(A$1:A$8,MATCH($A12;A$1:A$8,0),0),"") Previous
=IFERROR(INDEX(A$1:A$8,MATCH($A12,A$1:A$8,0),0),"") Current
In case it's not obvious there is a semicolon in the MATCH which should be a comma.

Thanks for your help.

 

[Skovgaard]

You're Welcome

Sorry, missed to correct one semicolon with comma, In my country we use the semicolon instead of comma

/Skovgaard