Finding the last [x] number of values

[Candy Shakes]

Hi,

I'm looking for a formula that searches for the last [x] number of values in another particular data range. I've attached a spreadsheet to help me explain. The sheet titled 'Data' contains all the data, the sheet titled 'Last 20' contains the sheet where I want to develop the formula.

In 'Last 20', I want to search for the person's name and find the last/final 20 values in the data range contained in 'Data'. So basically count back from the end, all the way to the beginning and find the last 20 numbers displayed. I want this to include for blanks and for a dash ("-"). Where it displays 'Date 1', 'Date 2', etc. I want to display the date that corresponds to each value. Remember that there are duplicate values in this set, not every value is unique.

In 'Last 20', the column titled 1 should be the last value, 2 should be the second last value, 20 should be the 20th to last value, and so on....

Thanks for any help in advance!

[Bo_Ry]

Is this right?

B4 copy to the right.
=INDEX(Data!$A$2:$BI$2,AGGREGATE(14,6,COLUMN(Data!$B$2:$BI$2)/ISNUMBER(Data!$B$2:$BI$2),B3))

[Candy Shakes]

Is this right?

B4 copy to the right.
=INDEX(Data!$A$2:$BI$2,AGGREGATE(14,6,COLUMN(Data!$B$2:$BI$2)/ISNUMBER(Data!$B$2:$BI$2),B3))

Amazing!

That's very close! I want the dashes included though, your formula ends up ignoring the dashes.

Would you know how to match the date to each value as well? I'd assume you would use some form of INDEX / MATCH formula, however the values aren't unique so I'm not sure if this would work...

[Bo_Ry]

This is to ignore blank cell

B4
=INDEX(Data!$A$2:$BI$2,AGGREGATE(14,6,COLUMN(Data!$B$2:$BI$2)/(Data!$B$2:$BI$2<>""),B3))

For the date, try below and change the format to date.

=INDEX(Data!$A$1:$BI$1,AGGREGATE(14,6,COLUMN(Data!$B$2:$BI$2)/(Data!$B$2:$BI$2<>""),B3))

[Candy Shakes]

I LOVE you! Lmao

This works great! I spent too much time on trying to work out a formula before I came to this forum, so thank you for your help!

One more issue though... I've plugged everything into my spreadsheet and it works great. However I am looking to search for 'John Smith' and his 'last 20 numbers'. These names will not be in order in both the 'Last 20' sheet and the 'Data' sheet.

At the moment, it just displays the numbers based on the first 10 rows that are in order in the data sheet. I have many random names and the order between both sheets is not the same. So it will display numbers that don't correspond to the person I'm searching for. That's why I need this exact formula to work in a search/vlookup/lookup type format.

Let me know if any of that doesn't make any sense.

[Bo_Ry]

LOL,

Plese Try this

=INDEX(Data!$A$2:$BI$9,MATCH($A4,Data!$A$2:$A$9,),AGGREGATE(14,6,COLUMN(Data!$B$2:$BI$2)/(INDEX(Data!$B$2:$BI$9,MATCH($A4,Data!$A$2:$A$9,),)<>""),B$3))

[Candy Shakes]

You are a genius mate! Thank you very much

It took me a bit of playing around to get it correct in my own spreadsheet, but it works absolutely 100% perfectly! I love the formula you gave as well, I had no idea how to use the aggregate or column formulas so I did some reading and they're more useful than I thought! It's a little complicated, but I guess the more experience the better.

[Bo_Ry]

Thank you, happy to help

More info for Aggregate.
https://support.office.com/en-us/art...rs=en-US&ad=US

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.