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


"ExcelMonkey" wrote:

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