I have a spreadsheet with a column of dates and a column containing either a value or null. The second column does not have any gaps in the array, so in theory it should be fairly simple. I would like a formula which will find the last entry in the 2nd column and display the corresponding date from the first column. i.e find the last date on which this particular thing was marked as done. I’ve tried VLOOKUP and INDEX but haven’t yet come up with the right combination. Thanks for any help you can offer!
It’s not entirely clear, what is the last value to look for?
And the formula is as follows:
=LOOKUP(9,9E+307;B2:B13;A2:A13)
The first parameter can contain any large number that obviously exceeds your data. For the example above, 100 is enough instead of 9.9E+307.
If you need to find the last occurrence for a certain record, then the second parameter of the formula must contain the corresponding condition (an array operation).
| Date | Heure de début | duréé du cours |
|---|---|---|
| Thu 17/06/21 | 14:15 | 0.75 |
| Thu 24/06/21 | 14:15 | 0.75 |
| Mon 05/07/21 | 12:45 | 0.75 |
| Thu 15/07/21 | 14:15 | 0.75 |
| Thu 22/07/21 | 14:15 | 0.75 |
| Thu 29/07/21 | 14:15 | 0.75 |
| Thu 02/09/21 | 14:15 | 0.75 |
| Thu 09/09/21 | 14:15 | |
| Thu 23/09/21 | 14:15 | |
| Thu 30/09/21 | 14:15 | |
| Thu 07/10/21 | 14:15 | |
| Thu 14/10/21 | 14:15 |
Thanks for your assistance. to clarify:
As you can see, the column with the dates is a series with intervals of usually but not exclusively one week. The column called durée du cours is the duration of a lesson. In my query, I want to return 02/09/21, being the latest date on which a lesson was given. Column entries in the 3rd column with null value should be ignored. There are no gaps in the sequence.
If so, the above formula works for your task. Instead of column B, put C.
Edit:
If the search does not find a large number, it will end at the last value and return the corresponding date from column A.
if there no gaps in C, simply:
=INDEX(A$2:A1000; COUNT(C$2:C1000))
edit by @karolus : Correction in Formula provided by @eeigor
It doesn’t work.
=INDEX(A:A; COUNT(C:C)+1) 'counts numbers only (w/o the col header), so add 1
Or:
=INDEX(A:A; COUNTA(C:C)) 'counts non-empty values
Thank-you! Typing the formula as written returns a 504 error but =INDEX(A$2:A$1000,COUNT(C$2:C$1000)) works like a charm.
More reliable, independent of the actual values and not using some made up upper value, would be
=LOOKUP(2;1/NOT(ISBLANK(B2:B13));A2:A13)
Oh sure. At first I answered without seeing the data.