Trying to get a formula to show last 5 most recent text values in a column corresponding to a name

[SW82SW]

Hi,

I am trying to find a formula that would look through all results in a column that correspond to a name in another column and have it show me the last 5 results for each person. I need results that show "Y" to be shown as "W", "N" to show as "L" and "V" to remain as "V" (W, L, or V). This list will grow over time so I need a formula that continues to only select the 5 most recent results.

I can't use XL2BB so I have attached a small table with an example of my data and a copy of what I would like. I have tried the following formula but it only shows the first letter for in each cell ("W") rather than what the last 5 results have been

=
CHOOSE(1+
  IFERROR(
    MOD(
      LARGE(
        +($B$1:$B1=B2)*(100*ROW($D:$D)+3*($D1:$D$1="Y")+2*($D1:$D$1="N")+1*($D1:$D$1="V")),
        {5,4,3,2,1}
      ),
      100
    ),
    0
  ),
  "W","L","V"
)

Game week	Tipper	Selection	Result
1	Brian	A	Y
1	Matthew	B	N
1	Jon	C	Y
2	Brian	D	N
2	Matthew	B	N
2	Jon	S	Y
3	Brian	T	Y
3	Matthew	A	N
3	Jon	B	Y
4	Brian	R	Y
4	Matthew	T	V
4	Jon	C	N
5	Brian	C	Y
5	Matthew	D	Y
5	Jon	A	Y

I would ideally like to output either of the below (one cell answer preferred)

Tipper	Form
Brian	W, W, W, L, W
Matthew	W, V, L, L, L
Jon	W, L, W, W, W

Tipper	Form
Brian	W	W	W	L	W
Matthew	W	V	L	L	L
Jon	W	L	W	W	W

Thanks guys.

 

[Cubist]

You're welcome.

 

[SW82SW]

@Cubist apologies to open this up again, but how would I adjust the formula you gave me to output the form across 5 cells? I think this might look better as I would be able to use conditional formatting to colour the cells depending on the outcome.

Thanks again.

 

[Cubist]

Try:

Book1
	M	N	O	P	Q	R	S	T	U	V	W
1
2
3
4
5
6
7
8
9
10	Game week	Tipper	Selection	Result		Tipper	Form1	Form2	Form3	Form4	Form5
11	1	Brian	A	Y		Brian	W	L	W	W	W
12	1	Matthew	B	N		Matthew	L	L	L	V
13	1	Jon	C	Y		Jon	W	W	W	L	L
14	2	Brian	D	N
15	2	Matthew	B	N
16	2	Jon	S	Y
17	3	Brian	T	Y
18	3	Matthew	A	N
19	3	Jon	B	Y
20	4	Brian	R	Y
21	4	Matthew	T	V
22	4	Jon	C	N
23	5	Brian	C	Y
24	5	Matthew	D	P
25	5	Jon	A	N
Sheet9

Cell Formulas
Range	Formula
S11:S13	S11	=IFERROR(XLOOKUP(INDEX(TAKE(FILTER($P$11:$P$25,($N$11:$N$25=[@Tipper])*($P$11:$P$25<>"P")),-5),1),{"Y","N","V"},{"W","L","V"}),"")
T11:T13	T11	=IFERROR(XLOOKUP(INDEX(TAKE(FILTER($P$11:$P$25,($N$11:$N$25=[@Tipper])*($P$11:$P$25<>"P")),-5),2),{"Y","N","V"},{"W","L","V"}),"")
U11:U13	U11	=IFERROR(XLOOKUP(INDEX(TAKE(FILTER($P$11:$P$25,($N$11:$N$25=[@Tipper])*($P$11:$P$25<>"P")),-5),3),{"Y","N","V"},{"W","L","V"}),"")
V11:V13	V11	=IFERROR(XLOOKUP(INDEX(TAKE(FILTER($P$11:$P$25,($N$11:$N$25=[@Tipper])*($P$11:$P$25<>"P")),-5),4),{"Y","N","V"},{"W","L","V"}),"")
W11:W13	W11	=IFERROR(XLOOKUP(INDEX(TAKE(FILTER($P$11:$P$25,($N$11:$N$25=[@Tipper])*($P$11:$P$25<>"P")),-5),5),{"Y","N","V"},{"W","L","V"}),"")

 

[SW82SW]

Thanks Cubist. One last thing, how do I convert "Y", "N" & "V" to "W", "L" & "V"?

 

[SW82SW]

I never noticed you had edited your answer to include that before I posted. Thank you.

 

[Peter_SSs]

Not a great deal of difference but if you want you could try this slightly shorter one.

SW82SW_1.xlsm
	N		P	Q	R	S	T	U	V	W
10	Tipper		Result		Tipper	Form1	Form2	Form3	Form4	Form5
11	Brian		Y		Brian	W	L	W	W	W
12	Matthew		N		Matthew	L	L	L	V
13	Jon		Y		Jon	W	W	W	L	L
14	Brian		N
15	Matthew		N
16	Jon		Y
17	Brian		Y
18	Matthew		N
19	Jon		Y
20	Brian		Y
21	Matthew		V
22	Jon		N
23	Brian		Y
24	Matthew		P
25	Jon		N
Sheet3

Cell Formulas
Range	Formula
S11:S13	S11	=MID("WLV",IFERROR(FIND(INDEX(TAKE(FILTER(P$11:P$25,(N$11:N$25=[@Tipper])*(P$11:P$25<>"P")),-5),1),"YNV"),4),1)
T11:T13	T11	=MID("WLV",IFERROR(FIND(INDEX(TAKE(FILTER(P$11:P$25,(N$11:N$25=[@Tipper])*(P$11:P$25<>"P")),-5),2),"YNV"),4),1)
U11:U13	U11	=MID("WLV",IFERROR(FIND(INDEX(TAKE(FILTER(P$11:P$25,(N$11:N$25=[@Tipper])*(P$11:P$25<>"P")),-5),3),"YNV"),4),1)
V11:V13	V11	=MID("WLV",IFERROR(FIND(INDEX(TAKE(FILTER(P$11:P$25,(N$11:N$25=[@Tipper])*(P$11:P$25<>"P")),-5),4),"YNV"),4),1)
W11:W13	W11	=MID("WLV",IFERROR(FIND(INDEX(TAKE(FILTER(P$11:P$25,(N$11:N$25=[@Tipper])*(P$11:P$25<>"P")),-5),5),"YNV"),4),1)