How do I look backwards for the most recent match of a text string in a column?

Question

I have an Excel sheet with various data entries that are in date order going down the page, with the dates in column A. I need a formula that will take a text string from an adjacent cell, then look back up a neighbouring column for the most recent match then return the date from column A.

Currently I have this formula in cell H100: =LOOKUP(G100,E100:E$5,A100:A$5).

I want it to look for the text in G100 in column E, going backwards to find the most recent example and then return the corresponding date from column A but despite the LOOKUP command being in reverse it always returns the first example in date order, not the most recent.

I would really appreciate some help from an expert, which I am not!

Answer 1

I would bring it into an Access database where that kind of data manipulation is easy. But then, I know how to use Access to do those kinds of things and I think it's much harder to do in Excel.

Answer 2

I am not certain to understand the question, but try

=OFFSET($A$1, 1+MATCH(G100, E$5:E100, 0)0,1,1)

this should catch the first (higher in the sheet) instance of the lookup match.