Need ideas on which formula(s) to use in this situation

[ker_01]

I have a worksheet with a lot of raw data. In one column I have an
identifier, in another I have dates, and in another column I have values.

I need to build a summary table on a new worksheet that will (given a date
and identifier) return the max value. I know MAX will get me the max value
overall, but not limited to just my double match. In the past I've used
things like sumproduct to pull multiple matches, but in this case I don't
want all the values returned, just the max value.

I don't need a polished formula, just some ideas on where to get started.

Thanks!
Keith

ID Date Value
--- ------ -------
A 1/4/06 12
B 1/4/06 8
A 1/4/06 11
D 1/4/06 7
G 1/5/06 14
F 1/1/06 12
A 1/5/06 17
D 1/3/06 8
B 1/4/06 5
V 1/3/06 9

so if I was checking A, 1/4/06 the result would be 12

 

[Glenn]

I have a worksheet with a lot of raw data. In one column I have an
identifier, in another I have dates, and in another column I have values.

I need to build a summary table on a new worksheet that will (given a date
and identifier) return the max value. I know MAX will get me the max value
overall, but not limited to just my double match. In the past I've used
things like sumproduct to pull multiple matches, but in this case I don't
want all the values returned, just the max value.

I don't need a polished formula, just some ideas on where to get started.

Thanks!
Keith

ID Date Value
--- ------ -------
A 1/4/06 12
B 1/4/06 8
A 1/4/06 11
D 1/4/06 7
G 1/5/06 14
F 1/1/06 12
A 1/5/06 17
D 1/3/06 8
B 1/4/06 5
V 1/3/06 9

so if I was checking A, 1/4/06 the result would be 12

You will likely get some formula options, but you might also want to consider a
PivotTable. You can place "Date" in the Page Fields, "ID" in the Row Fields and
"Value" in the Data Items (change from Sum to Max).

 

[Teethless mama]

=MAX(INDEX((A2:A11="A")*(B2:B11=--"1/4/2006")*C2:C11,))

 

[Bob Phillips]

=MAX(IF(A2:A20="A",C2:C20))

as an array formula, so commit with Ctrl-Shift-Enter, not just Enter.