Trying to find the last number in a row, but getting errors because of #N/A returns.

[AustSportingPix]

So my horse racing ratings are coming along, theyre finding winners and value runners.


My database table is a large file. In it, I want to find the peak return, average return (they're both done), plus the returns for each of the last 10 returns for each horse.

The table will have a column for each date from 2020/09/23 to as far as it will take me.

The problem is horses aren't running every day, so finding the returns for the last start to the 10th last isn't returning a value because the vLookups I'm using are returning a "#N/A" value if the horse didn't race that day.

The formula I'm using to find the last result is: =IFERROR(INDEX('All Horse Ratings.xlsx'!Allhorses[@[20200923]:[20201106]],1,COUNTA($Q2:$BI2)),"") (I change this in a few cells to get the last 10 results)
The formula I'm using to get the ratings into my database is: =IFNA(VLOOKUP($A2,INDIRECT("[ratings.xlsx]"&Allhorses[[#Headers],[20200923]]&"!$A:$C"),2,0),"")

What do I do to because I'm only getting errors or false returns.

 

[AustSportingPix]

deleted

 

[AustSportingPix]

You're welcome & thanks for the feedback.

no worries,

just one last question, to get the second last one i just change the one to -1?

 

[Fluff]

No, you would need

=IFERROR(INDEX($Q$1:$BK$1,AGGREGATE(14,6,(COLUMN($Q$1:$BK$1)-COLUMN($Q$1)+1)/($Q2:$BK2<>""),2)),"")

 

[AustSportingPix]

Again thanks

I used to be so good with basic excel, then used mac for 15 years.
If I could buy you a beer I would

 

[Fluff]

Glad to help & thanks for the feedback.