Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Complex LookUp / Match Problem ??
I need to monitor the "Thru-Put" assigned to each of our customers - this
table is called "Thru-Put". The Thru-Put is the sum of "Raw" ThruPut and Adjustments. I am looking for a formula to place in C that gives this result: BOXID Raw ThruPut Adjusted ThruPut BOX050 80 87 (80+2(GOOG)+1(MSFT)+4(QQQQ) BOX060 115 BOX234 1 My main table tracks the assignments - this table is called "Assignments". It looks like this: BOX ID SYMBOL BOX050 ELN BOX050 GOOG BOX050 MSFT BOX050 QQQQ BOX050 RFMD BOX050 RIG BOX050 YHOO My "adjustment" table looks like this - this table is called "Adjustments": Symbol Adjustment GOOG 2 IWM 1.67 MSFT 1 QQQQ 4 RIMM 1 SPY 1 So I think I need a formula for my Thru-Put Table that takes the value in ColA, looks for a match in the Assignments Table, and for each match found, check for a match on the Adjustment Table, if a match is found, add the adjustment value to the Raw Thru-Put value in the Thru-Put Table. Thank you in advance. |
#2
|
|||
|
|||
Carl
This array formula seems to fit the bill. In A1:B4: BOXID Raw ThruPut BOX050 80 BOX060 115 BOX234 1 In E1:F8: BOX ID SYMBOL BOX050 ELN BOX050 GOOG BOX050 MSFT BOX050 QQQQ BOX050 RFMD BOX050 RIG BOX050 YHOO In H1:I7 Symbol Adjustment GOOG 2 IWM 1.67 MSFT 1 QQQQ 4 RIMM 1 SPY 1 In C2: =B2+SUMPRODUCT(MMULT((A2=$E$2:$E$8)*($F$2:$F$8=TRA NSPOSE($H$2:$H$7)),$I$2:$I$7)) The formula must be entered with <Shift<Ctrl<Enter instead of just <Enter, also if edited later. If done correctly Excel will display the formula in the formula bar enclosed in braces { } Don't enter these braces yourself. They are Excel's way of showing, that the formula is an array formula. Copy C2 down. -- Best Regards Leo Heuser Followup to newsgroup only please. "carl" skrev i en meddelelse ... I need to monitor the "Thru-Put" assigned to each of our customers - this table is called "Thru-Put". The Thru-Put is the sum of "Raw" ThruPut and Adjustments. I am looking for a formula to place in C that gives this result: BOXID Raw ThruPut Adjusted ThruPut BOX050 80 87 (80+2(GOOG)+1(MSFT)+4(QQQQ) BOX060 115 BOX234 1 My main table tracks the assignments - this table is called "Assignments". It looks like this: BOX ID SYMBOL BOX050 ELN BOX050 GOOG BOX050 MSFT BOX050 QQQQ BOX050 RFMD BOX050 RIG BOX050 YHOO My "adjustment" table looks like this - this table is called "Adjustments": Symbol Adjustment GOOG 2 IWM 1.67 MSFT 1 QQQQ 4 RIMM 1 SPY 1 So I think I need a formula for my Thru-Put Table that takes the value in ColA, looks for a match in the Assignments Table, and for each match found, check for a match on the Adjustment Table, if a match is found, add the adjustment value to the Raw Thru-Put value in the Thru-Put Table. Thank you in advance. |
#3
|
|||
|
|||
Leo Heuser wrote...
.... In C2: =B2+SUMPRODUCT(MMULT((A2=$E$2:$E$8)*($F$2:$F$8=TR ANSPOSE($H$2:$H$7)),$I$2:$I$7)) .... Since your formula needs to be entered as an array formula, you could shorten it by replacing SUMPRODUCT with SUM. There's an alternative formula that doesn't require array entry. =B2+SUMPRODUCT(SUMIF($H$2:$H$7,LEFT(F$2:F$8,LEN(F$ 2:F$8)*(E$2:E$8=A2)),I$2:I$7)) This could be shortened as an array formula to =B2+SUM(SUMIF($H$2:$H$7,IF(E$2:E$8=A2,F$2:F$8),I$2 :I$7)) That said, conditional sums using multiple many-to-one relationships is one thing databases do much better than spreadsheets. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup then Match and insert value from next column | Excel Worksheet Functions | |||
Lookup Function Problem | Excel Discussion (Misc queries) | |||
lookup, index, match, offset, etc. | Excel Worksheet Functions | |||
Lookup Problem | Excel Worksheet Functions | |||
Find a match that;s not exact | Excel Worksheet Functions |