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

#### Ramenator

##### New Member
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!

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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

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.

Any other feedback, guys?

Replies
1
Views
387
Replies
5
Views
340
Replies
1
Views
202
Replies
0
Views
128
Replies
0
Views
148

1,203,238
Messages
6,054,312
Members
444,715
Latest member
GlitchHawk

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back