Lookup to multiple 2 tables

[Rolly_Sefu]

Hello,

I want to multiply the numbers from 2 table but only if they match

T1 data : {"a123";"a124";"a125";"a126";"a127";"a128"}
T1 values: {11;12;13;14;15;16}
T1 criteria: {"a123";"a125";"a128";0;0;0}

in order to keep T1's number sequence I used the formula ( =IF(ISNUMBER(MATCH(A3:A8;C3:C8;0));B3:B8;0) ) to get the values based T1 criteria
the result: T1: {11;0;13;0;0;16}

T2 data: {0;"a123";0;0;"a125";"a128"}
T2 values: {0;2;0;0;3;4}

How can I multiply T1 * T2 ( {11;0;13;0;0;16} * {0;2;0;0;3;4} ) - NOK

I would need to multiply {11;0;13;0;0;16} * {2;0;3;0;0;4}

Any ideas ?

 

[oldbrewer]

[TABLE="width: 512"]
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64, align: right"]3[/TD]
[TD="width: 64, align: right"]4[/TD]
[TD="width: 64"][/TD]
[TD="width: 64, align: right"]102[/TD]
[TD="width: 64, align: right"]3[/TD]
[TD="width: 64, align: right"]204[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]306[/TD]
[TD="align: right"]407[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]501[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]what do you mean by only multiply if they match ?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Rolly_Sefu]

Hey

I got the T1 {"a123";"a124";"a125";"a126";"a127";"a128"} with the values {11;12;13;14;15;16}

and T2 {0;"a123";0;0;"a125";"a128"} with the value {0;2;0;0;3;4}

I need to keep the same 6 item array format and have B1 from T1 * with vlookup(A1,T2,2,false).

T1 = A1 = a123 = 11 (is in the first position)

T2 = A2 = a123 = 2 (is in the second position - Not good)

So I need to multiply the value from a123 from T1 with the value of a123 from T2 but keep the same format ( array multiplication )

 

[Rolly_Sefu]

The down side is that I know how to create my function with the Small function and copy it down, but here I need the values as arrays since they will be multiplied with other numbers.

 

[oldbrewer]

sorry - don't understand what you want.....

 

[Rolly_Sefu]

I can not figure this out:

[TABLE="width: 1192"]
<tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Table 1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Table 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]=INDEX(F3:F7,IFERROR(MATCH(B3:B7,IF(F3:F7<>"",E3:E7),0),FALSE))
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]a123[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]a124[/TD]
[TD]12[/TD]
[TD][/TD]
[TD]a123[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]Row num evaluates to:[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]a125[/TD]
[TD]13[/TD]
[TD][/TD]
[TD]a127[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]=INDEX(F3:F7,{2;FALSE;FALSE;5;3})[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]a126[/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]I need to get the results from range E3:E7 - for the first array nr to come from the 2'nr row, the 2'nd array nr to be FALSE/0, 3'rd array nr to be the FALSE/0, 4'th array nr to be 5'th row nr, 5'th array nr to be 3'rd row nr[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]a127[/TD]
[TD]15[/TD]
[TD][/TD]
[TD]a126[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]In conclusion I need to multiple the matches between col A and C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]So that the result would be:[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]{11;0;0;14;15} * {2;0;0;4;3}[/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

see my post 2 - can you not ask your question in more simple terms eg I want to.....

 

[Rolly_Sefu]

I want to multiply all the items from T1 with all the items from T2 ( T1, a123 * T2, a123 => 11*2 ).

But I need it as an array, I can not use the small function, since the array will be multiplied with another array

or./

I want index function =INDEX(F3:F7,{2;FALSE;FALSE;5;3}) => to return {2;0;0;4;3} (2, 4, 3 ar the values from T2)

I hope this is a simpler explination.

 

[oldbrewer]

so re my post 2 there will be 81 totals - do you want a normal array or a 2-dimensional array ?

 

[bosco_yip]

I can not figure this out:

[TABLE="width: 1192"]
<tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Table 1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Table 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]=INDEX(F3:F7,IFERROR(MATCH(B3:B7,IF(F3:F7<>"",E3:E7),0),FALSE))
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]a123[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]a124[/TD]
[TD]12[/TD]
[TD][/TD]
[TD]a123[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]Row num evaluates to:[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]a125[/TD]
[TD]13[/TD]
[TD][/TD]
[TD]a127[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]=INDEX(F3:F7,{2;FALSE;FALSE;5;3})[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]a126[/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]I need to get the results from range E3:E7 - for the first array nr to come from the 2'nr row, the 2'nd array nr to be FALSE/0, 3'rd array nr to be the FALSE/0, 4'th array nr to be 5'th row nr, 5'th array nr to be 3'rd row nr[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]a127[/TD]
[TD]15[/TD]
[TD][/TD]
[TD]a126[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]In conclusion I need to multiple the matches between col A and C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]So that the result would be:[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]{11;0;0;14;15} * {2;0;0;4;3}[/TD]
[/TR]
</tbody>[/TABLE]

1] Your specification :

Multiply all the items from T1 with all the items from T2

={11;0;0;14;15} * {2;0;0;4;3}

=123 (desired result)

2] T1 Table and T2 Table as refer to post #.6

2.1] Enter array formula (CSE)

=SUM(IF(ISNUMBER(MATCH(A3:A7,C3:C7,0)),B3:B7,0)*IFERROR(VLOOKUP(T(IF({1},A3:A7)),C3:D7,2,0),0))

2.2] Formula breakdown :

=SUM(IF(ISNUMBER(MATCH(A3:A7,C3:C7,0)),B3:B7,0)*IFERROR(VLOOKUP(T(IF({1},A3:A7)),C3:D7,2,0),0))

become >>

=SUM({11;0;0;14;15}*{2;0;0;4;3})

become >>

=SUM({22;0;0;56;45})

Desired result >>

=123

Regards
Bosco