Pull data from 2 different Arrays in one cell

[jxbeeman]

Hi,
What i'm trying to do is use 1 Array as a lookup array and the other array
which contains the values that i want to lookup.
For example:
Array 1
Col A B
1 Top Part Sub Part
2 A AA
3 A AB
4 A AC
5 B BA
6 B BB


Array 2 (lets say this is something like order qty and is in another sheet)
Col F G
1 sub Part Qty
2 AA 1
3 BB 2
4 AC 2
5 AB 5

So say i wanted a summary for Part A (which contains AA, AB, AC).
The result would be (sum => AA+AB+AC => 1+2+5 = 8)
Or a summary for Part B = 2.

The problem i'm running into is the fact that these 2 arrays will not be
matched up (Arrays will be in separate worksheets). I've been working on it
and have come up with something along the following:
(Looking for Part "A")
= Sum(Vlookup( (Index(B2:B6, ((A2:A6="A")*Row(A2:A6)) ) F2:G5, 2, 0))
the result for a search on part "A" should yield = 8

This works on a line by line basis but not at an ARRAY level.
Any help would be greatly appreciated.

Thanks,
Josh

 

[smartin]

Hi,
What i'm trying to do is use 1 Array as a lookup array and the other array
which contains the values that i want to lookup.
For example:
Array 1
Col A B
1 Top Part Sub Part
2 A AA
3 A AB
4 A AC
5 B BA
6 B BB


Array 2 (lets say this is something like order qty and is in another sheet)
Col F G
1 sub Part Qty
2 AA 1
3 BB 2
4 AC 2
5 AB 5

So say i wanted a summary for Part A (which contains AA, AB, AC).
The result would be (sum => AA+AB+AC => 1+2+5 = 8)
Or a summary for Part B = 2.

The problem i'm running into is the fact that these 2 arrays will not be
matched up (Arrays will be in separate worksheets). I've been working on it
and have come up with something along the following:
(Looking for Part "A")
= Sum(Vlookup( (Index(B2:B6, ((A2:A6="A")*Row(A2:A6)) ) F2:G5, 2, 0))
the result for a search on part "A" should yield = 8

This works on a line by line basis but not at an ARRAY level.
Any help would be greatly appreciated.

Thanks,
Josh

Hi Josh,

Seems like I tried this two-level lookup before and failed utterly, but
this seems to work...

=SUM(IF(($A9=$A$2:$A$6),SUMIF($F$2:$F$5,$B$2:$B$6,$G$2:$G$5)))

where $A9 has the Top Part to evaluate ("A" or "B" in your example). I
showed where F and G are on the same worksheet for simplicity, but it
works fine if that range is moved to another sheet.

This must be array-entered.