Find last value, second last value, third last value etc.

[Whitestar127]

Hi everyone

Consider my example Excel sheet. Column B contains the match results in the form of either W (Win), D (Draw) or L (Loss), or "" if not any of those three. My actual formulas in column B work just fine ? I'm just using <Formula> here since this is just a small sample from my actual workbook.

Is there a way - using formulas - to get Excel to fetch the last three results for Arsenal? The last result should be put in cell H1, second last in G1, third last in F1. In my example sheet I have manually put in those values (W, L, D). But how can I make a formula that does this automatically?


EDIT: Full Excel uploaded (Ligatabell.xlsm)

[AliGW]

Welcome to the forum.

A good sample workbook has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[AliGW]

The 'updated' workbook is the same as before - there are still no expected results mocked up.

EDIT: I see now what you want. Do you not have MS365?

It would be MUCH easier if you did not replace formula results with <formula> - keep the formula results instead.

[Whitestar127]

Ok, so I should upload the full Excel file? I thought you wanted small sample files as examples.

[AliGW]

You can oversimplify your examples, and that's what you have done here.

Please confirm that your version is 2016 and not 365 - this would be super easy with the latter!

[Whitestar127]

Actually I have 365 at work and 2016 at home
I would want something that would work in both versions, but of course I welcome the solution for 365 if you only have that.

[Glenn Kennedy]

How are the expected answers the 3rd last... then there is only 2 W and 1 L & D????

[AliGW]

You still haven't provided a decent sample workbook. I asked you to get rid of <formula> and replace it with the value in those cells. You also need to supply data for more than one team. Your sample is OVER-SIMPLIFIED.

Here's a 365 solution for two teams:

=LET(f,FILTER($B$2:$C$38,($B$1:$C$1=E1)),ff,FILTER(f,f<>""),TRANSPOSE(TAKE(ff,-3)))

See the workbook - I have assumed that the formula returns blanks where there is no data. If it doesn't then you need to show what it does return. As I say, an over-simplified sample workbook is not really a lot of use.

[HansDouwe]

Please try in F1 and copy down:

=LET(t,B$2:C$38,c,INDEX(t,,MATCH(E1,B$1:C$1,0)),IFNA(TRANSPOSE(TAKE(FILTER(c,c>""),-3)),""))

[Whitestar127]

Thanks, I have uploaded the full Excel.

Apologies for not explaining properly. What i'm interested in is a formula that can extract the last 3 results for all the teams, to see their current form. So for Arsenal in this case (in the complete Excel-file) it's draw, loss and win (BA38, BA27, BA13). And I'd want those results in cells GN2, GM2 and GL2 respectively.

[AliGW]

There you go - a completely different set-up to what you provided in the original sample!

Back to square one - I'll have another look later (no time now - sorry).

[Whitestar127]

There you go - a completely different set-up to what you provided in the original sample!

Back to square one - I'll have another look later (no time now - sorry).

Not quite back to square one, because your formula works perfectly in my complete workbook, so thanks! I inserted this into cell GL2:

=LET(f;FILTER($BA$2:$BA$381;($BA$1:$BA$1=BA1));ff;FILTER(f;f<>"");TRANSPOSE(TAKE(ff;-3)))

The only thing now is to figure out how to do this in Excel 2016. Will it involve several steps maybe? Is it possible at all?

[AliGW]

It will involve more steps - someone else will be able to help you.

[HansDouwe]

The formulas that AliGW and I posted in post #8 and #9 also work in the complete workbook.

You just need to change the references.

Please try in GL2 and copy down:

=LET(t,BA$2:GD$381,c,INDEX(t,,MATCH(GK2,BA$1:GD$1,0)),IFERROR(TRANSPOSE(TAKE(FILTER(c,c>"")), -3)),""))

[Whitestar127]

The formulas that AliGW and I posted in post #8 and #9 also work in the complete workbook.

You just need to change the references.

Please try in GL2 and copy down:

=LET(t,BA$2:GD$381,c,INDEX(t,,MATCH(GK2,BA$1:GD$1,0)),IFERROR(TRANSPOSE(TAKE(FILTER(c,c>"")), -3)),""))

Thanks, I did that and it worked fine in Excel 365.
But I also need it to work in Excel 2016, and that's a bit more complicated apparently.

[HansDouwe]

Yes its more complicated, because Excel 2016 does not have the functions LET, FIILTER and TAKE