# Return most recent value for particular reference number in Excel

I have an Excel sheet with sales data for this year, with each row referring to a transaction. There are 47 columns but the important ones for my purposes are part code, unit price and date.

What I want is to be able to identify whether a particular transaction was the most recent for that particular part code. Example data below:

``````A   B           C            D
1   partcode    price   date
2   red-one     £50.00  01/06/2016
3   blue-two    £4.50   01/11/2016
4   green-three £1.00   20/10/2016
5   red-one     £45.00  01/11/2016
6   red-one     £47.00  02/11/2016
7   blue-two    £5.00   20/05/2016
8   yellow-four £31.50  30/10/2016
9   yellow-four £31.00  01/10/2016
``````

So my end goal is to be able to identify that the most recent price for red-one was £47, for blue-two £4.50, green-three £1 and yellow-four £31.50. I thought I might be able to do this be creating a helper column called Most Recent that would return TRUE in rows 2, 3, 5, and 7. I could then filter that in a Pivot Table quite easily. But I cannot figure out how to do those two things at once in one formula (i.e. subset by part code and check whether the date is the max in the range).

I think perhaps you mean "a helper column called Most Recent that would return TRUE in rows 3, 4, 6, and 8", as those are the rows that have the latest `date` for each `partcode`.
For your `Most Recent` helper column, you could use an array formula. One potential problem with this, however, is that array formulas are notoriously slow if they have to process a large number of rows. If that's not an issue for you, try the following:
1. In cell `E2`, type the following, but do not press the Enter key to accept it: `=MAX(IF(\$B\$2:\$B\$9=B2,\$D\$2:\$D\$9)) = D2`
3. Formula-copy cell `E2` down through cell `E9`