Return Array from Index/Match?

[EB08]

I feel like i'm overlooking something, but my goal is this: without the use of a helper column, use either index/match or another lookup function to return an array of values to then be used in sumproduct() or an array sum(). I cant seem to get any of the html makers to work on the office computer, or i would show a quick example directly from the sheet.

This is how i started to create the formula (which does not work) if it helps anyone understand the direction i'm trying to go with this.

Let me know if i need to get more specific. Thanks.

{=sum(index(B1:B3,match(D1:D3,A1:A3,0))*E1:E3)}

 

[Andrew Poulsom]

Can you post a snapshot of your data? Just copy it in Excel and paste it into a reply. You need to select 'Enhanced Interface - Full WYSIWYG Editing' in 'Message Editor Interface' under User CP|Edit Options (menu towards top of screen on Board).

 

[EB08]

Thanks for the tip; here's a generic sample of what i'm after. In actual use, which i dont think matters, the lookup table will be much shorter than the lookup value and Multiply by columns.


<TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=384 x:str><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" span=2 width=75><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" span=2 width=85><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 112pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: black 0.5pt solid; mso-ignore: colspan" class=xl29 height=17 width=150 colSpan=2 align=middle>Lookup Table</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl22 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 64pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #e0dfe3" class=xl27 width=85>L/U Values</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: silver; WIDTH: 64pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 width=85>Multiply By…</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl23 height=17 x:num>15</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:num>0.1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3"></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl23 x:num>10</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:num>20</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl23 height=17 x:num>5</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:num>0.3</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3"></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl23 x:num>15</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:num>20</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl25 height=17 x:num>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 x:num>0.2</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl25 x:num>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 x:num>20</TD></TR></TBODY></TABLE>


Columns are A, B, D, and E.

essentially, i'm trying to match the L/U value in column A to return the value in column B to be then be used in a sumproduct() situation with the values in column E.

Does this help?

 

[Andrew Poulsom]

So what would the result of the formula be with that data, given that you have 2 10's in column D?

 

[EB08]

I should have included that, my fault. The result in this case would be 10...what i'm looking for is something that would set up the first array in the formula below, which is the result of the the lookup for each L/U value.

=SUMPRODUCT({0.2;0.1;0.2}*{20;20;20})

 

[EB08]

Also, for anyone else looking at the thread, i'm trying to avoid creating a UDF...a straight-forward formula (if possible) is what i'm after. I've considered a UDF to create this array, but the sheet i'm applying this to (probably multiple times) is already very poorly designed, massive, and slow...so i'm assuming a UDF would just slow it down even further.

 

[Domenic]

Try...

=SUMPRODUCT(SUMIF($A$2:$A$4,D2:D4,$B$2:$B$4),$E$2:$E$4)

 

[EB08]

i see where you're going with this...but again, i realized afterwards that i've left out vital information; my apologies. I need to be able to use the less-than functionality of vlookup (i.e. vlookup(x,y,z,1) ) or of match (i.e. match(x,y,1) ). My lookup table will actually be in ascending order as its actually a set of age bands. I hope i havent left anything else out...


maybe this makes more sense:

<TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=384 x:str><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" span=2 width=75><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" span=2 width=85><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 112pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: black 0.5pt solid; mso-ignore: colspan" class=xl31 height=17 width=150 colSpan=2 align=middle>Lookup Table</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl24 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 64pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #e0dfe3" class=xl29 width=85>L/U Values</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: silver; WIDTH: 64pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 width=85>Multiply By…</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl25 height=17 x:num>5</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 x:num>0.1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3"></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl25 x:num>10</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 x:num>20</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl25 height=17 x:num>10</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 x:num>0.3</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3"></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl25 x:num>13</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 x:num>20</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl27 height=17 x:num>15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 x:num>0.2</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl27 x:num>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 x:num>20</TD></TR></TBODY></TABLE>


desired result would be 14

 

[Excelestial]

<TABLE style="WIDTH: 586pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=781><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 14pt; mso-width-source: userset; mso-width-alt: 658" width=18><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 332pt; mso-width-source: userset; mso-width-alt: 16201" width=443><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #e0dfe3 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 16.5pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #e0dfe3 1pt solid" class=xl66 height=22 width=64 align=right>15

</TD><TD style="BORDER-BOTTOM: #e0dfe3 1pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64 align=right u1:num>0.1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #efefef; WIDTH: 14pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #ece9d8" class=xl68 width=18> </TD><TD style="BORDER-BOTTOM: #e0dfe3 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #e0dfe3 1pt solid" class=xl69 width=64 align=right u1:num>10</TD><TD style="BORDER-BOTTOM: #e0dfe3 1pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl75 width=64 align=right u1:num>20</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #fcd5b4; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl78 width=64>4</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 332pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=443></TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #e0dfe3 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 16.5pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3 1pt solid" class=xl70 height=22 width=64 align=right u1:num>5</TD><TD style="BORDER-BOTTOM: #e0dfe3 1pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64 align=right u1:num>0.3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #efefef; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 width=18> </TD><TD style="BORDER-BOTTOM: #e0dfe3 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3 1pt solid" class=xl63 width=64 align=right u1:num>15</TD><TD style="BORDER-BOTTOM: #e0dfe3 1pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #3867a6 1pt solid" class=xl76 width=64 align=right u1:num>20</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl78>0</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 16.5pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3 1pt solid" class=xl71 height=22 width=64 align=right u1:num>10</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=64 align=right u1:num>0.2</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #efefef; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl73 width=18> </TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3 1pt solid" class=xl74 width=64 align=right u1:num>10</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #3867a6 1pt solid" class=xl77 width=64 align=right u1:num>20

</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" id=td_post_2453484 class=xl78>8</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl79> =SUMPRODUCT(--(I12:I14=F14)*(J12:J14)*
LOOKUP(F14,--($F$12:$F$14=F14),$G$12:$G$14))

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl79>interpreted as:
=SUMPRODUCT(--({10;15;10}=10)*({20;20;20})*
LOOKUP(10,--({15;5;10}=10),{0.1;0.3;0.2}))
</TD></TR></TBODY></TABLE>

 

[Domenic]

Try...

=SUMPRODUCT(LOOKUP(D2:D4,$A$2:$A$4,$B$2:$B$4),$E$2:$E$4)