#Value! Error with array index and match

Question

Is there anything obviously wrong with the following array index and match?

I can't work out the problem and all I get is a #Value! error with no further info.

{=INDEX(Sheet2!A:G,MATCH(1,(Sheet2!B:B=Sheet_1_crosstab!C2)*

(Sheet2!C:C=Sheet_1_crosstab!A2)*(Sheet2!D:D=Sheet_1_crosstab!T2),0),5,0}

Sheet 2 looks like this:

Locale  provider    source_aggregated   Week num    Desktop Mobile  Tablet
AT                     Clickouts            40           0    0       0
AT                     Clickouts            41           0    0       0
AT                     Clickouts            42           0    0       0
AT                     Clickouts            43           0    0       0

Sheet 1 looks like:

Locale  provider    source_aggregated   Week num    Desktop
AT                     Clickouts            40      ={indexmatch..         
AT                     Clickouts            41                     
AT                     Clickouts            42                     
AT                     Clickouts            43                    

I am trying to match the device split in sheet 2 to the overall traffic data in sheet 1 based on the locale, provider and source being matched.

Answer 1

The MATCH function expects a range of cells for the lookup_array argument.

You are likely passing either 1 or 0 as the result of multiplying boolean arrays.

That is

=(Sheet2!B:B=Sheet_1_crosstab!C2)*(Sheet2!C:C=Sheet_1_crosstab!A2)*(Sheet2!D:D=Sheet_1_crosstab!T2)

If valid, this will produce arrays of True False which will result in either True or False i.e. 1 or 0.

So in effect you have

=INDEX(Sheet2!A:G,MATCH(1,1,0),5,0)

or

=INDEX(Sheet2!A:G,MATCH(1,0,0),5,0)

Either will fail as the expected Match argument should be a range.

If you are trying to do a lookup consider a helper field that contains a unique key

Key field using concatenation of fields of interest to make unique

=CONCATENATE(B2,C2,D2,E2)

Key field:

Using the key to retrieve the Desktop numbers etc

=VLOOKUP(CONCATENATE(A2,B2,C2,D2),Sheet2!A1:H5,MATCH(Sheet1!E1,Sheet2!1:1,0),FALSE)

Note: I have used Match to find where desktop is in row 1 of Sheet2, this then defines the lookup column to return.

You would match Sheet1!F1 if Mobile was in that cell etc.

Lookups in sheet:

Remember to fix your ranges to absolute with $ where required.