if a cell equals any cell in a range

Question

I have an excel file:

A               B     C            D        |E            F
1| 27/50/4832   let   last,first   6786746  |08/29/1232   1243123
2| 25/30/9852   let   last,first   3453453  |08/23/1325   2453434
3| 05/10/9831   let   last,first   2453434  |08/03/1256   6786746
4| 15/00/9341   let   last,first   2342352  |09/45/1259   2453434

And I want to say

if a value from column f matches any from column D
then in column G write the date from E. 

A bit of a condition here is, not everything in F is unique. If it isn't unique, we would take the most recent date. As you see above, 2453434 is repeated twice in F. So G3 would say 08/23/1325 because it clearly happened almost 100 years later.

I have some tries (obviously incredibly simplistic), that aren't working and I can't get it to even start doing what I would like. And therefore can't get to the more complicated case of the most recent date.

=IF(F:F=D1,E1,"")
=IF(MATCH(D1,F:F,0),E1,"")
=IF(VLOOKUP(D1,F:F,1,FALSE)=D1,E:E,"die")

I would rather not use vba, if possible. But am open to the suggestion.

Answer 1

I would use something like this:

=INDEX(E:E,MATCH(1,(E:E=MAX(IF(F:F=D1,E:E,0)))*(F:F=D1),0))

assuming that your dates are correct.

Also, the above is an array formula, meaning that to make it work properly, you will have to hold Ctrl+Shift and press Enter after typing in the formula.

PS: Change the range appropriately, namely the E:E and F:F. If you have 200 rows, use E$1:E$200 and F$1:F$200 respectively so that the formula doesn't take an eternity to calculate.

This will retrieve the latest date from column E where the value from column D matches that from F.

For instance, it will put, in G1, 08/03/1256 (assuming valid date) since F3 matches D1 and will put in G3, 08/23/1325 (assuming valid date) since both F2 and F4 matches D3, but F2 is the later date.

Answer 2

Assuming that in your real data you have correct dates, use this one:

=IFERROR(LARGE(IF(D1=F:F,E:E,""),1),"")

with CTRL+SHIFT+ENTER

If you have dates like in your example above, that are out of range for excel (min date in excel 01/01/1900) getting max date is a real problem (actulally I'm not sure if it's really dates, since you have 09/45/1259 where 45 can't be day of month).

However, you can use this monster formula in G1:

=IFERROR(MID(LARGE(IF(D2=$F$1:$F$4,VALUE(RIGHT($E$1:$E$4,4)&LEFT($E$1:$E$4,2)&MID($E$1:$E$4,4,2)),""),1),5,2) & "/" & 
         RIGHT(LARGE(IF(D2=$F$1:$F$4,VALUE(RIGHT($E$1:$E$4,4)&LEFT($E$1:$E$4,2)&MID($E$1:$E$4,4,2)),""),1),2) & "/" &
         LEFT(LARGE(IF(D2=$F$1:$F$4,VALUE(RIGHT($E$1:$E$4,4)&LEFT($E$1:$E$4,2)&MID($E$1:$E$4,4,2)),""),1),4),"")

press CTRL+SHIFT+ENTER to evaluate it and drag it down. Also you can make formula shorter and faster using addtional column for LARGE(..). Note that I'm using $F$1:$F$4 instead F:F, because it makes formula much faster.