Matrix Math using LOOKUP inside Array {} Function

[ExcelMonkey]

This is a repost. I am trying to use a LOOKUP function in array formula
(CNTRL-SHIFT-ENTER). The formula I am using is in A18. It is as follows:

{=LOOKUP(A14:A16,$A$1:$A$12,B1:B12)}

What I am trying to do is generate an array of values within the cell (i.e.
after hitting F9) that should look like this:
{20%,20%,20%}

However, it is generating this instead:
{8.4;8.4;8.4} or as you can see in the cell - 8.400

Note that range A14:A16 will always have values from the range A1:A12. So
if it were "Bonus Revenue 1, R1","Bonus Revenue 1, R2","Bonus Revenue 1, R3"
the desired array would produce:
{30%,40%,140%}

Key point here is that I am eventually putting this function into a
sumproduct function to get:

{SUMPRODUCT({x,y,z},{30%,40%,140%})}

How do I generate this array? I can't seem to do it with a LOOKUP or an
INDEX function!!!!!

A B
1 None 10%
2 Equal Reg Alloc. 20%
3 Bonus Revenue 1, R1 30%
4 Bonus Revenue 1, R2 40%
5 Bonus Revenue 1, R3 140%
6 Bonus Revenue 1, R4 240%
7 Bonus Revenue 1, R5 340%
8 Bonus Revenue 1, R6 440%
9 Bonus Revenue 1, R7 540%
10 Bonus Revenue 1, R8 640%
11 Bonus Revenue 1, R9 740%
12 Bonus Revenue 1, R10 840%
13
14 Equal Reg Alloc.
15 Equal Reg Alloc.
16 Equal Reg Alloc.
17
18 8.400


Thanks

 

[Ron Coderre]

I'm not sure if your data has this flexibility....but.....

With this SORTED list in A1:B12
Bonus_Revenue_1,_R1 30%
Bonus_Revenue_1,_R10 840%
Bonus_Revenue_1,_R2 40%
Bonus_Revenue_1,_R3 140%
Bonus_Revenue_1,_R4 240%
Bonus_Revenue_1,_R5 340%
Bonus_Revenue_1,_R6 440%
Bonus_Revenue_1,_R7 540%
Bonus_Revenue_1,_R8 640%
Bonus_Revenue_1,_R9 740%
Equal_Reg_Alloc. 20%
None 10%

and....
B14: Bonus_Revenue_1,_R1
B15: Bonus_Revenue_1,_R2
B16: Bonus_Revenue_1,_R3

Then this formula
A18: =LOOKUP(A14:A16,A1:A12,B1:B12)
returns this:={0.3;0.4;1.4}

Also...if
A20: 100
A21: 200
A23: 300

Then this formula
A24: =SUMPRODUCT(A20:A22*LOOKUP(A14:A16,A1:A12,B1:B12))
returns 530
which is 100*0.3+200*0.4+300*1.4


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[daddylonglegs]

Hello Excel Monkey,

LOOKUP requires the lookup range, in your case A1:A12 to be sorted ascending
which, according to your data, isn't the case here. Try using SUMIF instead,
i.e.


=SUMPRODUCT({x,y,z},SUMIF($A$1:$A$12,A14:A16,B1:B12))

note: doesn't require CSE

 

[ExcelMonkey]

Hi Ron. Sorry for the delay - been tied up. I cannot guarantee that my list
will be sorted. As such, the LOOKUP function will not be appropriate. There
has to be way to do a lookup within an array formual that does not rely on a
sorted list. Need to figure it out how.

Thanks

EM

 

[Ron Coderre]

How about this....

With
A1:B12 containing your posted list (not sorted):
None 10%
Equal Reg Alloc. 20%
Bonus Revenue 1, R1 30%
Bonus Revenue 1, R2 40%
Bonus Revenue 1, R3 140%
Bonus Revenue 1, R4 240%
Bonus Revenue 1, R5 340%
Bonus Revenue 1, R6 440%
Bonus Revenue 1, R7 540%
Bonus Revenue 1, R8 640%
Bonus Revenue 1, R9 740%
Bonus Revenue 1, R10 840%

And
A14:A16 containing these 3 search values:
Equal Reg Alloc.
Bonus Revenue 1, R4
Bonus Revenue 1, R8

Then this formula returns a 3-number array that contains the percentages
associated with each search value.
A18: =LOOKUP(MATCH(A14:A16,A1:A12,0),{1;2;3;4;5;6;7;8;9;10;11;12},B1:B12)

In my example, those values are: {20%;240%;640%}

Or...They can be used, as you described, this way
A18:
=SUMPRODUCT({10000000;10000;100},LOOKUP(MATCH(A14:A16,A1:A12,0),{1;2;3;4;5;6;7;8;9;10;11;12},B1:B12))

That formula returns
=SUMPRODUCT({10000000;10000;100}*{20%;240%;640%})
=10000000*20%+10000*240%+100*640%
=2024640

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP