return results based on multiple conditions

[Drabbacs]

The quick version;

What formula is used to retreive the values from columns
C, D, E, etc based on the values of columns A and B? It
would be like a vlookup but for 2 column conditions, not
row column intersect. And how is it extended to 3 column
conditions? Further, how would you incorporate that into
adding the results of several simultaneous lookups from
the same source data(see below)?

I know I've seen the answer to this question before but I
can't find it again.

The longer version;

I want to return the results based on 2 and possibly 3
conditions.

for example:

( conditions ) ( results )
A B C D E
- -- --- -- ----
1| A A1 A1A 10 1000
2| A A1 A1B 20 2000
3| A A2 A2A 30 3000
4| A A3 A3A 40 4000
5| A A3 A3B 50 5000
6| B A1 A1A 60 6000
7| B A2 A2A 70 7000
8| B A2 A2B 80 8000
9| B A3 A3A 90 9000

In cell F1 the user specifies A
In cell G1 the user specifies A1
In cell I1 the result should be 10 and in I2 it should be
1000. That is I want the first row that corresponds to the
conditions for column A and B.

Future needs may require a 3 condition lookup.
F1 = B, G1 = A2, H1 = A2B then I1 should return 80 and J1
should return 8000.

I'd also like to extend this to enable multiple value
lookups for each of the columns. For example F1 = A, F2 =
B, G1 = A1, H1 = A1A, should return the sum of 10 & 60 and
the sum of 1000 & 6000.

I know how to do that for one column already, thanks to
Peo. The array formula looks like:

{=SUM((INDEX(source01,,1)=TRANSPOSE($F$1:$F$10))*(INDEX
(source01,,4)))}

where source01 is the named range.

Thanks for any help in advance. If you know a web source
with a solution laid out already, that works for me.
Drabbacs

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+sHIFT+ENTER):
=INDEX(C1:C100,MATCH(1,($A$1:$A$100="condition1")*($B$1:$B$100="conditi
on2"),0))
and copy this to the right

 

[Drabbacs]

Thanks. Now for the second part, how do I combine the
formulas below?


{=SUM((INDEX(source01,,1)=TRANSPOSE($F$1:$F$10))*(INDEX
(source01,,4)))}

This formula pulls and sums the information in the 4th
column of range source01 if it finds a value listed in
F1:f10 in the first column of source01.

------
{=INDEX(D1100,MATCH(1,($A$1:$A$100="condition1")*
($B$1:$B$100="condition2"),0))}

This formula pulls column D if the conditions are found in
column A and column B.


What I want is the sum of results from column D(4th
column) of source01 if conditions 1 and 2 are specified,
but the conditions may have multiple entries.

Thanks in advance
Drabbacs

 

[Aladin Akyurek]

Why not use an additional column to concatenate values from A, B, and C...

In F1 enter & copy down:

=A1&CHAR(127)&B1&CHAR(127)&C1

2-key lookup...

G1 houses A and H1 A1.

In J1 enter:

=MATCH(G1&CHAR(127)&H1&"*",$F$1:$F$9,0)

In K1 enter & copy across:

=IF(ISNUMBER($J1),INDEX(D$1$9,$J1,"")

3-key lookup...

G1 houses B, H1 A2, and I1 A2A.

In J1 enter:

=MATCH(G1&CHAR(127)&H1&CHAR(127)&I1,$F$1:$F$9,0)

In K1 enter & copy across:

=IF(ISNUMBER($J1),INDEX(D$1$9,$J1,"")