multi-lookup & multi-condition

[Drabbacs]

This is a addon question to an earlier discussion. Thanks
to everyoneso far who has helped.

The situation:

My data is 3 columns (A, B, and C) of indexing information
(alphanumeric) and several (around 14) columns of
numerical information.

I want to allow the user to be able to specify *multiple*
conditions for columns A, column B and column C and then
sum the data from the numerical information.

Currently I can specify one condition in each column and
retrieve the desired output. The array formula looks like
this:

=INDEX(D$221100,MATCH(1,($A$2:$A$21100=$V2)*
($R$2:$R$21100=$U2)*($S$2:$S$21100=$W2),0))

where D holds the desired output,
A, R, and S are the indexing information, and
V2, U2, and W2 are where the user specifies the conditions.

Or I can specify multiple conditions in one column using
the following array formula (from a different worksheet):

=SUM((INDEX(source01,,1)=TRANSPOSE(A1:A10))*(INDEX
(source01,,2)))

Where A1:A10 is the user inputs for specifying conditions
and source01 is a named range which includes an index
column and an output column.


I want to combine these 2 formulas. I want to let the user
be able to specify multiple conditions for columnA,
multiple conditions for columnB and multiple conditions
for columnC and then return the sum of outputs that match
any combination of the conditions.

Can anyone help me combine these two formulas?

In an earlier post, it was suggested that I should
concatenate A, B, and C and do lookups on the
concatenation. That would work if I only wanted 1
combination. I need something that allows ((A or B or C) &
(Q or Z) & (X))

Thanks in advance.
Drabbacs

 

[Bernie Deitrick]

Drabbacs,

When using Array formulas, the way to construct an OR is to use addition +

((A1:A100="A")+(A1:A100="B"))

This means if equal to A or B..

You can work that into your formulas to whatever degree you desire.

HTH,
Bernie
MS Excel MVP

 

[Drabbacs]

I don't see how that helps. How would that be incorporated
into the problem I described?

 

[Bernie Deitrick]

Drabbacs,

With additional criteria in row 3,

=INDEX(D$221100,MATCH(1,(($A$2:$A$21100=$V2)+($A$2:$A$21100=$V3))*
(($R$2:$R$21100=$U2)+($R$2:$R$21100=$U3))*(($S$2:$S$21100=$W2)+($S$2:$S$2110
0=$W3)),0))

HTH,
Bernie
MS Excel MVP