D
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
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