Lookup value column A, return value column C from most recent date in column B.

[Garadactyl]

It may seem a bit confusing from the title, but that is because I am a bit confused by it as well.

I have a table that has 3 columns.

A. Date Ordered
B. Tooling ID
C. Cost

12/19/12	12341234	$200.00
1/20/13	43214321	$150.00
3/23/13	56785678	$1000.00
5/25/13	12341234	$238.00
9/28/13	12341234	$250.00

<tbody>
</tbody>

This table is filled out when a Tool is purchased. On the current sheet I have over 700 entries.
We just recieved a new program that we will order our tooling through instead of doing it over the phone, and will track all this information for us. The only thing, is we have to input the most recent prices for the tooling in ourselves, which we currently only have in the excel sheet.

What I want to do, is create another table that has just the Tooling ID and the Cost. But I want a formula in the cost column that will search the Purchase order Log for the Tooling ID, and return the cost from the row that has the most recent date in it. So for example, If the first Tooling ID on the list is 12341234, then it will look in the above table, find the 3 values "12341234" and see that $250.00 is the most recent, and place that value in.

This way I can just import the new excel sheet into the program. If anyone has a formula that will do this for me, or even a quick macro, please let me know. If I confused you at all, please ask me what i meant, this is a huge project for me, and if I do it by hand while responsible for all my other tasks, this could take me weeks. I want to have it done by the end of this week for the boss.

 

[Andrew Poulsom]

Like this?

Excel 2010
	A	B	C	D	E
1	Date	ID	Cost
2	12/19/2012	12341234	$200.00	12341234	$250.00
3	01/20/2013	43214321	$150.00
4	03/23/2013	56785678	$1,000.00
5	05/25/2013	12341234	$238.00
6	09/28/2013	12341234	$250.00
Sheet1

Cell Formulas
Range	Formula
E2	=LOOKUP(9.99999999999999E+307,(B2:B6=D2)*(A2:A6),C2:C6)

 

[Garadactyl]

It seems as if that is exactly what I need. I just tried it, but didnt work out. The thing is, not all Tooling IDs that are on the master list are on the purchase order log. With that said, I tried your formula with the first Tooling ID on my list, and it returned $66. But when I went and checked the PO Log, that Tooling ID isnt even in there because it has not been purchased in the last few years. Why did I get $66?

*EDIT* I just tried it on a Tooling Id that I know is on the log, and it returned $66 again. I made sure that I shanged my ranged and cell numbers correctly. Could you explain the 9.99999999999999E+307 in the formula?

 

[Andrew Poulsom]

My mistake, sorry. Try:

=INDEX(C2:C6,MATCH(MAX(INDEX((B2:B6=D2)*(A2:A6),)),INDEX((B2:B6=D2)*(A2:A6),),FALSE))

 

[AhoyNC]

If you have Excel 2007 or later you can do it without a formula.
Copy your table to a new worksheet or workbook (NOTE: this is important as this method is going to remove duplicates, so you will lose all other data).

Highlight your copied table.
Go to SORT on RIBBON

-Sort first by Tooling ID
-then sort by Date Ordered (in the drop down box for order change order to = NEWEST TO OLDEST)

Go to REMOVE DUPLICATES on ribbon
-Uncheck all the column headers but Tooling ID.
-OK

This will leave you with a table of only the most current date, ID and Price.

 

[Garadactyl]

This seems to be working great. The only thing is now, if there is a Tooling ID that is on the MAster list, but not on the PO log because we haven't purchased it in the last few years, it is entering a random number, I believe that this is the first cost on the sheet. Is there a way to make it return a blank cell if it does not match the tooling ID?

 

[Andrew Poulsom]

Try:

=IF(COUNTIF(B2:B6,D2),INDEX(C2:C6,MATCH(MAX(INDEX((B2:B6=D2)*(A2:A6),)),INDEX((B2:B6=D2)*(A2:A6),),FALSE)),"")