Return array from INDEX function?

Question

I have to do a computed fields in Excel table which summarize values from a table joined with values from a domain table. E.g. have table of goods with name and count and domain table pricelist with goods name and price. The goal is to lookup price for each goods row, multiply it by the price and sum this all together. Some of the goods lines may repeat and some may miss so it is not possible to do the vector product.

In fact the real case is a bit more complicated.

I can do a lot with MATCH and INDEX functions and array formulas. But my showstopper is that INDEX function cannot return array of found values based on array of indices passed in. This formula always ends up with value 1, not an array as I would expect:

{=COUNT(INDEX({1,2,3},{3,2,1}))}

Just note that I cannot use LOOKUP functions as they require sorted lists...

Would it help to use VLOOKUPs, which can handle unsorted lists when you set the optional 4th argument to FALSE or 0? — chuff
– chuff, Commented Dec 13, 2012 at 21:42

barry houdini · Accepted Answer · 2012-12-13 23:11:44Z

2

INDEX (like VLOOKUP) doesn't return an array of values (except in some convoluted circumstances)

If you have Name1 and Count in first table and Name2 and Price in second table then you can get the grand total price with this formula

=SUMPRODUCT(Count,SUMIF(Name2,Name1,Price))

[The SUMIF part is doing what I assume you are trying to do with INDEX]

Assumes that Name2 in second table has no repeats

edited Dec 13, 2012 at 23:11

answered Dec 13, 2012 at 21:53

barry houdini

46.9k8 gold badges67 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kuba Over a year ago

EXCELlent, thats it. All the time, I was trying to "vectorize" the inputs, not the conditions!

Collectives™ on Stack Overflow

Return array from INDEX function?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related