Complex LookUp / Match Problem ??

C

carl

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.
 
L

Leo Heuser

Carl

This array formula seems to fit the bill.

In A1:B4:
BOXID Raw ThruPut
BOX050 80
BOX060 115
BOX234 1
Click to expand...


In E1:F8:
BOX ID SYMBOL
BOX050 ELN
BOX050 GOOG
BOX050 MSFT
BOX050 QQQQ
BOX050 RFMD
BOX050 RIG
BOX050 YHOO
Click to expand...


In H1:I7
Symbol Adjustment
GOOG 2
IWM 1.67
MSFT 1
QQQQ 4
RIMM 1
SPY 1
Click to expand...


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.
 
H

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))
Click to expand...
....

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.
 
Top