Array search (Vlookup, Hlookup, AverageIF) function help please

[LukeNZ]

Hi,
I have a table in Sheet (Advanced Process Signature), with a date in cell O1, and a range of tags in N2:N97.

In Sheet (Data) I have in cells B1:AO1 the same tags (as column headings), with dates in A2:A8642 (multiple date entries for each date).

I want a formula to return the average value (Sheet APS, cells O2:O97) for all values on selected Date (O1), for the corresponding tag (N2:N97) (i.e. looking across the 2nd sheet to find the tag, then looking down to find the dates)

This works based on averaging the total data set for the tag number (entered as an array formula)

Please Login or Register  to view this content.

But when I try to also add the date portion I get it wrong. Can someone help with the second lookup part?

Please Login or Register  to view this content.

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

[LukeNZ]

Attached.
Data is an example of raw data download.
APS is the sheet with formulas on it.

[Richard Buttrey]

Hi

Try in O7 copied down

Please Login or Register  to view this content.

It could be simplified if the values in column A of the Data were not Date/Time numbers, and just dates. If so lose the second citeria in the function and change the first criteria to a simple $O$1

Obviously change the row number 2523 as necessary. Better still create a dynamic range name for column B and substitute the range name in place of the $B$2:$B$2523

[LukeNZ]

Perfect! Works great thanks Richard!