Using a variation of the table below, i'm trying to figure out a formula that will: Return the latest date (or largest #) in the "Date" column from all rows where a value from "Value" column is found. (e.g. if i'm looking for "a", then it should return the latest date (from "Date" where a is found in "Value") = 1/21/2010 Value Date a 11/15/2009 a 11/16/2009 b 11/17/2009 b 11/18/2009 c 11/19/2009 c 12/20/2010 c 12/21/2010 a 12/22/2010 a 1/01/2010 b 1/02/2010 c 1/20/2010 a 1/21/2010

Hi, This can be done with array formulas. With your data in columns B and C with the first data (a) in B4, enter a, b and c in cells D4, D5 and D6 In cell E4 enter this formula: =MAX(IF(B4:B15=$D$4,C4:C15,0)) Note the $ signs, Now with your cursor inside the formula in the formula bar, press Ctrl+Shift+Enter at the same time. If you get this right, the formula will have curly braces around it: {=MAX(IF(B4:B15=$D$4,C4:C15,0))} The formula won't drag, so enter these in E5 and E6, =MAX(IF(B4:B15=$D$5,C4:C15,0)) =MAX(IF(B4:B15=$D$6,C4:C15,0)) after each, use the Ctrl+Shift+Enter triplet to make the formula into an array formula. This gave me the following: B C D E 3 ID Date Sort ID Max 4 a 15-Nov-09 a 22-Dec-10 5 a 16-Nov-09 b 2-Jan-10 6 b 17-Nov-09 c 21-Dec-10 7 b 18-Nov-09 8 c 19-Nov-09 9 c 20-Dec-10 10 c 21-Dec-10 11 a 22-Dec-10 12 a 1-Jan-10 13 b 2-Jan-10 14 c 20-Jan-10 15 a 21-Jan-10 Regards

Done. Thank you for your help! BTW, the drag works if i remove the $ for the reference cell :) Thank you again!

Hi, Array formulas only become array formulas when you use the Ctrl+Shift+Enter. If you just hit Enter, the formula doesn't get the curly braces around it and it returns the wrong result. Entering curly braces from the keyboard doesn't work either - so it has to be the Ctrl+Shift+Enter. Array formulas run a series of individual formulas on the whole table and internally hold the results from each stage. In this example the IF(B4:B15=$D$4,C4:C15,0) part of the formula was repeated 12 times starting with IF(B4=$D$4,C4,0) and ending with IF(B15=$D$4,C15,0) Because D4='a', this formula returned zero if the value in column B was not 'a' If the value was 'a' it returned the date in the same row. It then ran the MAX function on the 12 individual results. Regards

Hi, You said that dragging the formula worked if you removed the $ from the D4 part of the formula. You should check the other two formulas and see what range of source data they are pointing to. Click in the formula bar for cell E6 and see the ranges highlighted - they will be two rows out. As your latest dates were all near the end of the table, you still got the right results. With different data you will get wrong results. Regards PS after entering an array formula you may need to use Ctrl+Shift+Enter again. Always check that the Curly braces {} are there.

Thank you again for your help, The curly braces setting the array is the lesson learned in this instance :) I've determined that i can drag the formula and to use it anywhere on the column too. I changed my reference columns to be B:B and C:C to query (as it were) the entire columns. the result is: {=MAX(IF(B:B=D4,C:C,0)))} Gold! Thanks again!

Hi, Thanks for the feedback. Glad it works, and interesting that you can drag the formula if you reference the complete column - I shall remember that ! Regards