=INDEX(array,MATCH(array,array))

[eibi]

Given the data in range A1:B6:

black, 2
brown, 1
green, 3
orange, 6
pink, 3
purple, 2

and a set of test values in range D1:D10

green
purple
pink
orange
purple
brown
black
green
orange
pink

When I create an array formula:

{=MATCH(D1:D10,A1:A6,0)}​

The formula returns an array:

{3;6;5;4;6;2;1;3;4;5}​

Good. I can use this array as input for all manner of other formulas.

But. Suppose I want to use it in an INDEX() formula to lookup and sum the corresponding value (from column B) for all the test values in column D.

I would expect:

{=SUM(INDEX(B2:B6,MATCH(D1:D10,A1:A6,0)))}​

to return:

{=SUM(INDEX(B2:B6,{3;6;5;4;6;2;1;3;4;5}))}​

and then:

{=SUM({3;2;3;6;2;1;2;3;6;3})}​

resolving to a friendly 31

But instead, it ignores the MATCH() array:

{=SUM(INDEX(B2:B6,MATCH(D1:D10,A1:A6,0)))}​

and behaves as though I wanted it to:

{=SUM(INDEX(B2:B6,MATCH("green",A1:A6,0)))}​

which resolves to a useless 3

I really want to use an =INDEX(array,MATCH(array,array)) application. But I can't figure it out.

A helper column is an unsavory work-around.

Any suggestions? Am I doing it wrong?

 

[azumi]

try this array formula:

=SUM(SUMIF(A1:A6;D1:D10;B1:B6))

 

[XOR LX]

I would expect:

{=SUM(INDEX(B2:B6,MATCH(D1:D10,A1:A6,0)))}​

​

Perhaps you meant B1, not B6?

The construction requires a few small additions in order to coerce an array of returns:

=SUM(INDEX(B1:B6,N(IF(1,MATCH(D1:D10,A1:A6,0)))))

with CSE.

http://excelxor.com/2014/09/05/index-returning-an-array-of-values/

Regards

 

[eibi]

@azumi -- Great workaround, and a great solution for the example I provided. However, my actual application isn't always a "sum" function -- sometimes I need to return an array of text strings and further evaluate them, so I'm looking for a way to pass an array as the first argument to a MATCH() nested in an INDEX().

@XOR LX -- I'm already a follower of your blog, where I've learned so many new ideas -- but it has so much content in such a concise format that I digest it very slowly. I will give your suggestion some study and try it out in a few different applications.

Thanks all!!!

 

[KenU]

@eibi,

Try the array formula:
=SUM(MMULT(--($D$1:$D$10=TRANSPOSE($A$1:$A$6)),($B$1:$B$6)))
using CSE.

The Sum function can be left off to result in a vector of the results which would replicate an INDEX / MATCH.

There is a more complete description (along with a challenging problem) in the discussion http://chandoo.org/forum/threads/hit-the-wall-with-index.13996/

Hope that helps.

Regards,
Ken