?
Solved

Index/match in a single cell array formula

Posted on 2010-04-07
14
Medium Priority
?
400 Views
Last Modified: 2012-05-09
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

0
Comment
Question by:prophet001
  • 6
  • 3
  • 2
  • +2
14 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 30036269
Preston,

Can you post an example file?

Patrick
0
 
LVL 16

Expert Comment

by:RichardSchollar
ID: 30036615
Hi Preston

Does this work for you:

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

Richard
0
 

Author Comment

by:prophet001
ID: 30037566
Here is a sample of the functionality that i am trying to implement.
Preston
ArrayBook.xlsx
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:prophet001
ID: 30037839
Richard,
 i get a value error
i was trying =sumproduct(values, vlookup(values, lookup_array, 2))
Preston
0
 
LVL 16

Accepted Solution

by:
RichardSchollar earned 2000 total points
ID: 30038180
Preston

See formula in cell C16.

Richard
ArrayBook.xlsx
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 30038414
Preston,
Use this formula...
=SUMPRODUCT((E6:E10)*LOOKUP(E6:E10,B6:B10,C6:C10))
Saurabh...
0
 

Author Comment

by:prophet001
ID: 30039886
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
 
LVL 16

Expert Comment

by:RichardSchollar
ID: 30040062
Preston

Have you actually been reading any of my posts?
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 30040291
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 Comment

by:prophet001
ID: 30040456
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 Comment

by:prophet001
ID: 30040689
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 30040823
Hi Richard!
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 30041037
Richard? Somebody told me he was Rokkie of the year once......
Hello Preston,
To answer your question.......
Neither VLOOKUP or INDEX/MATCH can return an array of values when the lookup value is an array or range (in normal circumstances), whereas LOOKUP can.....
regards, barry
0
 

Author Comment

by:prophet001
ID: 30041570
Thank you barry.

Preston
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question