Using IF(AND(..)) with arrays

Hi guys.

This is my first post on this forum, but I have been following for a while.

I have a workbook with two sheets "DATA" and "OVERVIEW".

In DATA I have a bunch of different arrays:
Colm. A: Unit identifier (eg. Shampoo)
Colm. B: Dates (03-12-2004)
Colm. C: User identifier (eg. Michael)

In OVERVIEW:
Colm. A: Unit identifier (eg. Shampoo rows 2-81, then Conditioner for the next 80 rows, etc.)
Colm. B. User identifier (eg. Michael, Lars, etc. (there is a total of 80 users), repeating for all types of units).
Row 1: Dates

So what I am trying to do is:
=IF(AND(DATA!$A:$A=$A4;DATA!$B:$B=$B4;DATA!$C:$C=C$1);DATA!$K:$K;"")
DATA!$A:$A is the unit array in DATA sheet
$A4 is one of the units, that occurs in the data array in DATA sheet
If this unit actually is in array A:A in DATA, then if:
DATA!$B:$B is the user identifier array in DATA sheet
$B4 is one of the user names, that occurs in the data array in DATA sheet
If the user actually occurs in the B:B array of DATA and the unit from A4 is in the same row, then if the date in C1 is in the same row as where the unit and name was identified:
DATA!$C:$C=C$1
Return the cell value of the given row from column K in DATA.
DATA!$K:$K

However, nothing is returned - where am I failing?

Im desperat for your help

Originally Posted by teylyn

Please upload a workbook with some sample data.

This looks like it can be done with a pivot table, or, as an alternative, array formulas. But nobody will be keen to type up the sample data to produce the POC.

cheers,

I should have uploaded - why didn't I think of that

Anyway here it is ... the test focus solely on "Shampoo", and does actually return ONE value for three users, whereas it should return five values for Louis (23-05-2011=3, 27-05-2011=2, etc.)

Originally Posted by daddylonglegs

Typically you can't use AND in array formulas because that function returns a single value rather than an array. One option is to use SUMIFS (assuming your data in column K is numeric) but as this looks like a "lookup" type formula you can use a Lookup, try this formula

=IFERROR(LOOKUP(2;1/(DATA!$A:$A=$A4)/(DATA!$B:$B=$B4)/(DATA!$C:$C=C$1);DATA!$K:$K);"")

probably better, though, to restrict the ranges to the data range rather then use the whole column

That actualyl works - thanks a lot. Only problem now is (I just realized), that my overview sheet contains 170*84 rows, and rougly 3000 columns, approximatly 43 mio. cells. Now my computer is a beast but no way it will cope with that. Any suggestions? Would a VBA script make sense? :|

Originally Posted by teylyn

You can use a pivot table to show that information with just a few clicks. No need to enter formulas.

See attached.

cheers,

Thanks for the input. Maybe pivot is the best way to handle that amout of data - I'll give it a shot