Array formula with index + match

[Guest]

I am using (index + match) formula to create reports from a list of items. If
I need to add 2 or more items from the list I replicate the formula with a
plus like this..

=INDEX(TB,MATCH(I37,CODE,0),MATCH($F$20,A,0)) +
INDEX(TB,MATCH(J37,CODE,0),MATCH($F$20,A,0))

Some places I need to add about 10 items from the list, so it is very hard.

Is there away, that I can use a array formula or some thing similar to give
the 'lookup' value for match function like below...
={sum(INDEX(TB,MATCH(I39:N39,CODE,0),MATCH($F$20,A,0)))}

I have used named ranges TB, CODE, MATCH

Thanks

 

[T. Valko]

Try this:

=SUMPRODUCT(--(ISNUMBER(MATCH(I39:N39,CODE,0))),INDEX(TB,,MATCH(F20,A,0))

Biff

 

[Guest]

What does "--" mean ? and how is it entered in formulae ?

 

[T. Valko]

I should have tested that formula!

Try this one (tested!):

=SUMPRODUCT(--(ISNUMBER(MATCH(code,I39:N39,0))),INDEX(TB,,MATCH(F20,A,0)))

What does "--" mean ? and how is it entered in formulae ?

It's entered just the way you see it. Type them then hit ENTER.

(ISNUMBER(MATCH(code,I39:N39,0)))

will return an array of TRUE and FALSE

The "--" is coercing the logical values TRUE and FALSE to numeric 1's and
0's. 1 for TRUE and 0 for FALSE.

--(ISNUMBER(MATCH(code,I39:N39,0)))

For more info see:

http://xldynamic.com/source/xld.SUMPRODUCT.html


http://mcgimpsey.com/excel/formulae/doubleneg.html

Biff

 

[Guest]

Super it worked ..... many thanks !

One more clarification - why there is no value in index formulae for row
number ?

 

[T. Valko]

why there is no value in index formulae for row number ?

The data you want to sum is in a column. We use INDEX/MATCH to find that
column.

The rows to sum are determined by --(ISNUMBER(MATCH(code,I39:N39,0))).

Where that condition is TRUE (coerced to 1) the corresponding row from the
column found in the INDEX/MATCH is then summed.

Biff