I have a column with cells that contain months separated with commas. I need to return the nth from last month.
Example dataset:
| Months | Nth from Last | Result |
|---|---|---|
| 2024 May, 2023 October, 2023 May | 2 | 2023 October |
| 2023 October, 2023 August, 2023 May, 2023 January | 3 | 2023 August |
| 2024 March, 2024 February | 2 | 2024 March |
So based on the Months column I want to return the nth value (defined in the Nth from Last column) on the Result column. Note, this is looking from oldest to newest with the cells organized from newest to oldest. This will always be the case.
>Solution :
You may try:
=map(A2:A,lambda(Σ,if(Σ="",,choosecols(split(Σ,", ",),-offset(Σ,,1)))))
If you just want a single-cell formula, it would be:
=choosecols(split(A2,", ",),-B2)