[VLOOKUP] 2 values - how do I get the newer value??

[valdimin]

Hi folks,
I have a specific question regarding VLOOKUP, which I cant solve. Hope you can help me:

I have 2 tables:


Table 1 has the following content:

Transaction ID....Status....Turnover
123.................In Process...2000 EUR
124.................In Process...1000 EUR
125.................Open..........800 EUR

Table2 has the following content:

Transaction ID......Activity ID...Date
123....................505..........22.11.2014
128....................509..........22.10.2014
123....................506..........10.12.2014

As you can see, Transaction 123 unfortunately is assigned twice in table 2, each time with a different activity ID (505 and 506).

What I want to achieve:

In table 1 I want to create a new column where the activity with the newest date should be shown!

In my example with transaction 123 it would be activity 506:

Transaction ID....Status....Turnover......Activity ID
123.................In Process...2000 EUR.....506

--> Any idea how I can achieve this with Excel?

Thank you!

Regards.

[ragulduy]

Assuming your table is in A1:A3 I would try something like and the most recent will always be the last I would try something like:
=INDEX(B1:B3,MAX((A1:A3=A5)*ROW(A1:A3)))
as an array formula (confirm with ctrl+shift+enter)