Excel help: how to reference & return data...

Microsoft Excel 2007
October 27, 2009 at 11:28:01
Specs: Windows Vista
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

See More: Excel help: how to reference & return data...

Report •

October 27, 2009 at 12:59:43

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:
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:

The formula won't drag, so enter these in E5 and E6,
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		


Report •

October 27, 2009 at 13:07:22
Done. Thank you for your help!

BTW, the drag works if i remove the $ for the reference cell :)

Thank you again!

Report •

October 27, 2009 at 13:20:06
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.


Report •

Related Solutions

October 27, 2009 at 13:28:13

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.

PS after entering an array formula you may need to use Ctrl+Shift+Enter again. Always check that the Curly braces {} are there.

Report •

October 27, 2009 at 13:40:01
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:


Thanks again!

Report •

October 27, 2009 at 14:10:54

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 !


Report •

Ask Question