Index/match in a single cell array formula

Hi,
I was wondering if someone might be able to help me. I have been trying to get an array formula which combines index and match to return a sum over a range of values and have had no success. I have a small range of cells that contain dollar amounts e.g. a1:a4, i then have a range that contains the lookup values e.g. c1:d5, i am trying to lookup a markup value in the c1:d5 range to multiply the a1:a4 values by and then return a sum which represents the sum of the individually marked up values. I can get excel to do this in a multiple column format by breaking the formula into its parts but i can't get it to this in one cell. here is what i have tried:

{=SUM(INDEX(\$D\$1:\$D\$5, MATCH(\$A\$1:\$A\$4, \$C\$1:\$C\$5)))}

Any help on this would be greatly appreciated. I haven't even had luck returning simple arrays from the index function (match returns arrays without issue)

e.g. {=SUM(INDEX(\$D\$1:\$D\$5, {1,2,3}))}

i would expect the previous formula to return the sum of the values of cells D1:D4 but it doesn't.
Thank you for any help :)

Preston

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Preston,

Can you post an example file?

Patrick
0
Commented:
Hi Preston

Does this work for you:

=SUMPRODUCT(A1:A4,LOOKUP(A1:A4,C1:D5))

Richard
0
Author Commented:
Here is a sample of the functionality that i am trying to implement.
Preston
ArrayBook.xlsx
0
Author Commented:
Richard,
i get a value error
i was trying =sumproduct(values, vlookup(values, lookup_array, 2))
Preston
0
Commented:
Preston

See formula in cell C16.

Richard
ArrayBook.xlsx
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
Preston,
Use this formula...
=SUMPRODUCT((E6:E10)*LOOKUP(E6:E10,B6:B10,C6:C10))
Saurabh...
0
Author Commented:
Saurabh,
Thank you for the answer. That works in the case of same sized arrays. What do I do when the markup table is only 4 rows and the values table is n rows (10, 15, etc)? sumproduct fails on having unsymmetrical arrays.
Preston
0
Commented:
Preston

Have you actually been reading any of my posts?
0
Commented:
Doesn't Richard's formula do the job, i.e.
=SUMPRODUCT(E6:E8,LOOKUP(E6:E8,B6:C10))
Note that the range E6:E8 is not the same number of rows as B6:C10
regards, barry
0
Author Commented:
Hey Richard, sorry about that I missed your earlier post. Thank you. That works and is the same solution as Saurabh however how do I account for multiple values (greater than the lookup_array rows) without getting VALUE errors?
0
Author Commented:
Richard,
That works, i was able to change the array sizes and have it work correctly. Thank you you were the first post and I will give the points to you. One other question. Why can't i use vlookup or a combination of index/match to accomplish this? I read that lookup is deprecated and is only provided for backward compatibility.
Thank you again,
Preston
0
Commented:
Hi Richard!
0
Commented:
Richard? Somebody told me he was Rokkie of the year once......
Hello Preston,