Hi, each week I add a new row to a table, I add the date and a value. I am trying to find out the most recent date that I had entered the same value. I can do it Excel using a combination of index, small & row but cannot workout how to do it in Numbers as there is no support for arrays, is there.
I can count the number of occurrences of the new number, I can then subtract 1 to workout that I need the nth occurrence of the same value in the column. Once I’ve identified the row of the nth occurrence I could then use index to find the corresponding date. I’ve considered adding a hidden column to do some of the working out but have been unable to make this work.
Any help appreciated
Regards, Ian.
I need to know the last date the same value had a occurred in a sequential table of dates and numbers
Device: Mac Running Catalina
App: Numbers
—–
Ian
You can do that many ways. A quick one would be to use the new XMATCH function.
So if you have dates in column B, then in column C you could have the check for the last entry with the same date. If the table currently goes to row 75, then this would be the formula in C75
=XMATCH(B75,B$2:B74,0,-1)
The first parameter is the date to check. So the cell to the left. The second is the range to check. Notice the range starts with B$2, with the $ indicating the "Preserve Row" for that value. So when this formula is moved to C75, the range would be B$2:B75. The 74 changes to a 75, but the 2 stays the same.
The last two parameters are 0 and -1, which stand for "Exact Match" and "Last To First." So the last match is found, not the first one.
Note that the result is the position of the value in B$2:B74. So if you get 15 then the match is in row 16 because there is a header row and you are starting with B2 not B1.
Gary, thank you XMATCH helps, I had hoped that using 'exact or next smallest' look up the values column looking for an exact match or lower and then stop when the criteria is met but XMATCH is correctly prioritising a search of the whole list before looking the next smallest so finds exact matches from years ago. I could get the right result using a binary search with XLOOKUP but my values are not in order. Any further help appreciated.
Ian: Do you need to match two values in each row? Then do what you suggested and create a new column (hidden if you like) that concatenates the two values. Like in D75 put =B75&","&C75. Then use the =XMATCH(D75,D$2:D74,0,-1) in column E to find the last value with both B and C matching the current B and C.