Hello,
In a table with chronologically sorted values, what Excel formula will display the date corresponding to the
last time a given value appears in the table?
For example, suppose you have a spreadsheet with values in the range A1:B12 as in the following screenshot (Fig. 1):
Also, suppose a user-entry box is placed at cell B15 (Fig. 2).
What formula in cell C15 will return the date from the table corresponding to the last occurrence of the user entry?
Note that in the above example, the user entry "EAT" occurs in the table only once (06/06/14). However, suppose the user value occurs more than once in the table, as in the following screenshot (Fig. 3).
Here, the entry "DEW" occurs twice (01/25/14 & 07/11/14) but according to the initial question, the formula in C15 should always display the most recent date, in this case 07/11/14.
What formula would accomplish that?
Thanks
=LOOKUP(2,1/(B1:B12=B15),A
Flyster