Need help with a formula

[EmergencyStats]

Hi everyone!

I have been asked to lead a presentation for my company. I have a shortened list of some of our sales staff and a longer list with all staff. Is there a way to transpose a list of quarterly performance from the shorter list onto the longer one whilst keep any gaps? I am fully a novice with Excel so I can explain more if its unclear.

Thanks!

Example sales.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Sales	1st Quarter	2nd Quarter	3rd Quarter	4th Quarter				All Sales	1st Quarter	2nd Quarter	3rd Quarter	4th Quarter
2	Addie	Near target	Near target	Near target	Near target				Addie
3	Aziz			Probation	Probation				Aziz
4	Chris	On target	Near target	Near target	On target				Chris
5	David	On target	On target	On target	On target				Danny
6	Debbie	Near target	On target	On target	Near target				David
7	Frank	Needs improvement	Needs improvement	Near target	Needs improvement				Debbie
8	Gary	Near target	Near target	Near target	Near target				Frank
9	Glenn	On target	On target	On target	On target				Gabbie
10	Jenny	On target	On target	On target	On target				Gary
11	John	On target	Near target	Near target	On target				Glenn
12	Judith	On target	On target	On target	On target				Jenny
13	Julie	Probation	Needs improvement	On target	On target				John
14	Lin	On target	On target	On target	On target				Judith
15	Mo	Needs improvement	Near target	Near target	Needs improvement				Julie
16	Paul	Near target	On target	Needs improvement	On target				Lin
17	Sid	Needs improvement	Near target	Near target	Needs improvement				Michelle
18	Stephen	Needs improvement	Needs improvement	Needs improvement	Needs improvement				Mike
19	Steve	On target	Needs improvement	Needs improvement	On target				Mo
20	Tom		Probation	Probation	Probation				Mohammad
21	Tyler	Near target	On target	On target	Near target				Paul
22									Sarah
23									Sid
24									Stacey
25									Stephen
26									Steve
27									Toby
28									Tom
29									Tyler
Sheet1

 

[Sufiyan97]

Hi & Welcome to MrExcel.

Try

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Sales	1st Quarter	2nd Quarter	3rd Quarter	4th Quarter				All Sales	1st Quarter	2nd Quarter	3rd Quarter	4th Quarter
2	Addie	Near target	Near target	Near target	Near target				Addie	Near target	Near target	Near target	Near target
3	Aziz			Probation	Probation				Aziz	0	0	Probation	Probation
4	Chris	On target	Near target	Near target	On target				Chris	On target	Near target	Near target	On target
5	David	On target	On target	On target	On target				Danny	#N/A	#N/A	#N/A	#N/A
6	Debbie	Near target	On target	On target	Near target				David	On target	On target	On target	On target
7	Frank	Needs improvement	Needs improvement	Near target	Needs improvement				Debbie	Near target	On target	On target	Near target
8	Gary	Near target	Near target	Near target	Near target				Frank	Needs improvement	Needs improvement	Near target	Needs improvement
9	Glenn	On target	On target	On target	On target				Gabbie	#N/A	#N/A	#N/A	#N/A
10	Jenny	On target	On target	On target	On target				Gary	Near target	Near target	Near target	Near target
11	John	On target	Near target	Near target	On target				Glenn	On target	On target	On target	On target
12	Judith	On target	On target	On target	On target				Jenny	On target	On target	On target	On target
13	Julie	Probation	Needs improvement	On target	On target				John	On target	Near target	Near target	On target
14	Lin	On target	On target	On target	On target				Judith	On target	On target	On target	On target
15	Mo	Needs improvement	Near target	Near target	Needs improvement				Julie	Probation	Needs improvement	On target	On target
16	Paul	Near target	On target	Needs improvement	On target				Lin	On target	On target	On target	On target
17	Sid	Needs improvement	Near target	Near target	Needs improvement				Michelle	#N/A	#N/A	#N/A	#N/A
18	Stephen	Needs improvement	Needs improvement	Needs improvement	Needs improvement				Mike	#N/A	#N/A	#N/A	#N/A
19	Steve	On target	Needs improvement	Needs improvement	On target				Mo	Needs improvement	Near target	Near target	Needs improvement
20	Tom		Probation	Probation	Probation				Mohammad	#N/A	#N/A	#N/A	#N/A
21	Tyler	Near target	On target	On target	Near target				Paul	Near target	On target	Needs improvement	On target
22									Sarah	#N/A	#N/A	#N/A	#N/A
23									Sid	Needs improvement	Near target	Near target	Needs improvement
24									Stacey	#N/A	#N/A	#N/A	#N/A
25									Stephen	Needs improvement	Needs improvement	Needs improvement	Needs improvement
26									Steve	On target	Needs improvement	Needs improvement	On target
27									Toby	#N/A	#N/A	#N/A	#N/A
28									Tom	0	Probation	Probation	Probation
29									Tyler	Near target	On target	On target	Near target
30
Sheet1

Cell Formulas
Range	Formula
J2:M29	J2	=INDEX($B$2:$E$21,MATCH($I2,$A$2:$A$21,0),MATCH(J$1,$B$1:$E$1,0))

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Sales	1st Quarter	2nd Quarter	3rd Quarter	4th Quarter				All Sales	1st Quarter	2nd Quarter	3rd Quarter	4th Quarter
2	Addie	Near target	Near target	Near target	Near target				Addie	Near target	Near target	Near target	Near target
3	Aziz			Probation	Probation				Aziz			Probation	Probation
4	Chris	On target	Near target	Near target	On target				Chris	On target	Near target	Near target	On target
5	David	On target	On target	On target	On target				Danny
6	Debbie	Near target	On target	On target	Near target				David	On target	On target	On target	On target
7	Frank	Needs improvement	Needs improvement	Near target	Needs improvement				Debbie	Near target	On target	On target	Near target
8	Gary	Near target	Near target	Near target	Near target				Frank	Needs improvement	Needs improvement	Near target	Needs improvement
9	Glenn	On target	On target	On target	On target				Gabbie
10	Jenny	On target	On target	On target	On target				Gary	Near target	Near target	Near target	Near target
11	John	On target	Near target	Near target	On target				Glenn	On target	On target	On target	On target
12	Judith	On target	On target	On target	On target				Jenny	On target	On target	On target	On target
13	Julie	Probation	Needs improvement	On target	On target				John	On target	Near target	Near target	On target
14	Lin	On target	On target	On target	On target				Judith	On target	On target	On target	On target
15	Mo	Needs improvement	Near target	Near target	Needs improvement				Julie	Probation	Needs improvement	On target	On target
16	Paul	Near target	On target	Needs improvement	On target				Lin	On target	On target	On target	On target
17	Sid	Needs improvement	Near target	Near target	Needs improvement				Michelle
18	Stephen	Needs improvement	Needs improvement	Needs improvement	Needs improvement				Mike
19	Steve	On target	Needs improvement	Needs improvement	On target				Mo	Needs improvement	Near target	Near target	Needs improvement
20	Tom		Probation	Probation	Probation				Mohammad
21	Tyler	Near target	On target	On target	Near target				Paul	Near target	On target	Needs improvement	On target
22									Sarah
23									Sid	Needs improvement	Near target	Near target	Needs improvement
24									Stacey
25									Stephen	Needs improvement	Needs improvement	Needs improvement	Needs improvement
26									Steve	On target	Needs improvement	Needs improvement	On target
27									Toby
28									Tom		Probation	Probation	Probation
29									Tyler	Near target	On target	On target	Near target
Master

Cell Formulas
Range	Formula
J28:M29,J25:M26,J23:M23,J21:M21,J19:M19,J10:M16,J6:M8,J5,J9,J17:J18,J20,J22,J24,J27,J2:M4	J2	=XLOOKUP(I2,$A$2:$A$100,$B$2:$E$100&"","")
Dynamic array formulas.

 

[EmergencyStats]

Hi & Welcome to MrExcel.

Try

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Sales	1st Quarter	2nd Quarter	3rd Quarter	4th Quarter				All Sales	1st Quarter	2nd Quarter	3rd Quarter	4th Quarter
2	Addie	Near target	Near target	Near target	Near target				Addie	Near target	Near target	Near target	Near target
3	Aziz			Probation	Probation				Aziz	0	0	Probation	Probation
4	Chris	On target	Near target	Near target	On target				Chris	On target	Near target	Near target	On target
5	David	On target	On target	On target	On target				Danny	#N/A	#N/A	#N/A	#N/A
6	Debbie	Near target	On target	On target	Near target				David	On target	On target	On target	On target
7	Frank	Needs improvement	Needs improvement	Near target	Needs improvement				Debbie	Near target	On target	On target	Near target
8	Gary	Near target	Near target	Near target	Near target				Frank	Needs improvement	Needs improvement	Near target	Needs improvement
9	Glenn	On target	On target	On target	On target				Gabbie	#N/A	#N/A	#N/A	#N/A
10	Jenny	On target	On target	On target	On target				Gary	Near target	Near target	Near target	Near target
11	John	On target	Near target	Near target	On target				Glenn	On target	On target	On target	On target
12	Judith	On target	On target	On target	On target				Jenny	On target	On target	On target	On target
13	Julie	Probation	Needs improvement	On target	On target				John	On target	Near target	Near target	On target
14	Lin	On target	On target	On target	On target				Judith	On target	On target	On target	On target
15	Mo	Needs improvement	Near target	Near target	Needs improvement				Julie	Probation	Needs improvement	On target	On target
16	Paul	Near target	On target	Needs improvement	On target				Lin	On target	On target	On target	On target
17	Sid	Needs improvement	Near target	Near target	Needs improvement				Michelle	#N/A	#N/A	#N/A	#N/A
18	Stephen	Needs improvement	Needs improvement	Needs improvement	Needs improvement				Mike	#N/A	#N/A	#N/A	#N/A
19	Steve	On target	Needs improvement	Needs improvement	On target				Mo	Needs improvement	Near target	Near target	Needs improvement
20	Tom		Probation	Probation	Probation				Mohammad	#N/A	#N/A	#N/A	#N/A
21	Tyler	Near target	On target	On target	Near target				Paul	Near target	On target	Needs improvement	On target
22									Sarah	#N/A	#N/A	#N/A	#N/A
23									Sid	Needs improvement	Near target	Near target	Needs improvement
24									Stacey	#N/A	#N/A	#N/A	#N/A
25									Stephen	Needs improvement	Needs improvement	Needs improvement	Needs improvement
26									Steve	On target	Needs improvement	Needs improvement	On target
27									Toby	#N/A	#N/A	#N/A	#N/A
28									Tom	0	Probation	Probation	Probation
29									Tyler	Near target	On target	On target	Near target
30
Sheet1

Cell Formulas
Range	Formula
J2:M29	J2	=INDEX($B$2:$E$21,MATCH($I2,$A$2:$A$21,0),MATCH(J$1,$B$1:$E$1,0))

Thanks, this works! And thank you for the quick reply.

 

[Sufiyan97]

You're welcome.
Glad we could help!