Complex LookUp / Match Problem ??

[Guest]

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.

 

[Leo Heuser]

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=TRANSPOSE($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.

 

[Harlan Grove]

Leo Heuser wrote...
....

In C2:

=B2+SUMPRODUCT(MMULT((A2=$E$2:$E$8)*($F$2:$F$8=TRANSPOSE($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.