Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pull data from 2 different Arrays in one cell
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pull data from 2 different Arrays in one cell
jxbeeman wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to pull data from the same cell in multiple workbooks. | Excel Discussion (Misc queries) | |||
how to pull data from a specific cell on each tab as I add tabs | Excel Worksheet Functions | |||
ALIGNING TWO ARRAYS OF DATA VALUE | Excel Worksheet Functions | |||
Reading data arrays from multiple data files in excel | Excel Discussion (Misc queries) | |||
pull data for a company with data in diff cells multiple wrkshts | Excel Worksheet Functions |