Search and return column value from table based on highest date in another column

[Ramenator]

Good day everyone! I was looking for some help on making the following search work.

Would like to search for the value of E3 in the table to the left, and then return the value in B based on the highest date in C.

So the value in F3 should be 10/17/2014

Spent a couple of minutes on it but couldn't make it work, and figured somebody here ought to know. I know it involves some MAX, IF, and possible a VLOOKUP/INDEX+MATCH. Who knows, I may have been completely off.

Thanks and appreciate any help as always!

 

[ar0sa]

I'm thinking this may be easier in VBA, but assuming that your sheet will be sorted by Column A, this should work;

=INDEX(INDIRECT("A"&MATCH(E2,A:A,0)&":C"&MATCH(E2,A:A,0)+COUNTIF(A:A,E2)-1),MATCH(MAX(INDIRECT("C"&MATCH(E2,A:A,0)&":C"&MATCH(E2,A:A,0)+COUNTIF(A:A,E2)-1)),INDIRECT("C"&MATCH(E2,A:A,0)&":C"&MATCH(E2,A:A,0)+COUNTIF(A:A,E2)-1),0),2)

It's lengthy and messy, but I think it's the only way to group by multiple criteria. There may be a 100% simpler way to do it, but this is what I've got. I'd give it a few test cases first though, and let me know if you'd like me to try and explain it.

Good luck!

- Tony

 

[Ramenator]

I'm willing to use a VBA/UDF as well. The reason being the data is formatted as a table and sorting might change on a whim.

 

[Ramenator]

Any other feedback, guys?