Lookup the most recent occurance of an input, and the return the value from another column

[D.Lovell]

Hi All,

Please see the attached example....


Column A has a line number.

Column B has dates and there are duplicates.

Column C is the lookup value.

Column D is the value to return.


I want to return the value in column D, where column C has the most recent occurrence of something, I have just used a 1 letter text example 'A'.

This should take into account both the date and the line number. Where there are multiples of 'A' entered into column C for the same date, I want to find the lowest one down in the list by line number, and then return the value in column D.


Many thanks in advance for any insight,

[southward]

Give this a try:

=INDEX(D1:D26,MATCH(MAX(IF(C1:C26="A",A1:A26))&"A",A1:A26&C1:C26))

It is an array formula that has to be entered with CTRL/SHIFT/ENTER.

Cheers

[D.Lovell]

Thank you, yes this ideal...

Now can we also modify this to return the value in Column D for the 'first' most recent occurrence... So the lowest line number for the most recent date?

I have indicated what I mean on the updated example.

[AlKey]

For the most recent

=INDEX(D2:D26,LARGE(IF(C2:C26="A",ROW(C2:C26)-1),1))

First occurrence

=INDEX(D2:D26,LARGE(IF(C2:C26="A",ROW(C2:C26)-1),2))

Both are array formulas
**Must be entered with Ctrl+Shift+Enter key combination.

[D.Lovell]

This is ideal, thanks both for the insight