That formula is flawed for a couple of reasons. It assumes there will be
enough data to satisfy the result of the MATCH function. Also, even if there
is enough data the result of the MATCH could easily return the incorrect
result because there will probably be many duplicates of the lookup_value
and when used with a match_type argument of 0, will always match the first
instance of the lookup_value.

That formula is basically looking for the last TEXT entry in the column and
then returning the value that is offset from that location by -6 rows, -5
rows, -4 rows, etc.

Try this formula:

I'm assuming that AN1 is your column header and your actual data starts in
AN2 on down.

=IF(COUNTA(AN$2:AN$100)<7-ROWS($1:1),"-",INDEX(AN2:AN100,LARGE(INDEX((AN$2:AN$100<"")*RO W(AN$2:AN$100),,1),7-ROWS($1:1))-1))

Copy down to a total of 6 cells. Also, use a large enough range size to
allow for newly added data. Or, you could use a dynamic range that will
automatically adjust as you add new data.

How to create a dynamic range:

http://contextures.com/xlNames01.html#Dynamic

--
Biff
Microsoft Excel MVP


" wrote in message
ups.com...
Hi

With some help I got a formula that works, but only after a fashion

Here was my original question:
Return last 6 results formula
I have a column in my sheet that fills in progressively over a season
as team results are entered.

This particual column will show d for a goal-less draw (0-0), D for a
score draw (1-1, 2-2 etc) and W or L for Win/Loss

I'd like to show the last 6 game 'form' in this format: W,L,W,W,d,D
using that column.
If less than 6 games I want it to put in dashes (-,-,W,L,d,D)

With kind help, this was the result:
=IF(INDEX(AN:AN,MATCH(LOOKUP(REPT("z",255),AN:AN), AN:AN,
0)-6)=0,"-",INDEX(AN:AN,MATCH(LOOKUP(REPT("z",255),AN:AN),AN :AN,0)-6))

Next cell down has -5, next cell -4 etc etc
(A final cell concatenates to give the result I wanted)
What doesn't work is if there are less than 6 results I get #VALUE as
the result

Can someone kindly
a) Possibly explain why
b) Explain what the formula actually does!

Thanks
Neil