=LOOKUP(2,1/(B1:B12=B15),A

Flyster

Solved

Posted on 2014-08-31

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

In a table with chronologically sorted values, what Excel formula will display the date corresponding to the

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

10 Comments

Above your data range put headers Date & Entry (or something more relevant).

Above your User Entry and Date formula put the same headers, B15 & C15 in the example when an extra row has been inserted to accommodate the headers and then use this formula:

=DMAX($A$1:$B$13,$C$15,$B$

Thanks

Rob H

Flyster, your solution:

=LOOKUP(2,1/(B1:B12=B15),A

is the easiest to use but the most difficult for me to understand.

I have seen (and I now use) a number of solutions — usually from Barry Houdini — which include a reciprocal argument and an equation as a single term. However, I don't understand their function or how they work.

As a result, I will shortly be opening a couple of new threads to see if I can obtain explanations. The titles are as follows:

• Range of cells equal to a single value in Excel functions

• Using an equation as a term in Excel functions

• Reciprocal arguments in Excel functions

Thanks

Tom, thanks for the response.tomfarrar

Could you just sort the date column descending and use a simple vlookup? Wouldn't that pick up the last date for the user?

Yes. Your solution would work perfectly well for the example I included. However, when an example such as mine is used in a question, I think it is implied* that the best solution is one that functions correctly:

• for a list (database) of any size and,

• for a list that may be dynamic (ie getting additional entries or deletions of entries), and

• with as little manipulation as possible.

Therefore, although your solution would display the correct solution for a list of any size, the list would continually require resorting (manipulation) any time anything changes — which in many databases you may not be aware of.

Title | # Comments | Views | Activity |
---|---|---|---|

Excel VBA question, if cell is empty then replace it with specified value | 8 | 42 | |

Third Sunday of the Month | 10 | 50 | |

Excel 2013 Drop down help with data | 3 | 35 | |

Excel remove Chars in Conditional formating. | 6 | 19 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**21** Experts available now in Live!