Array formula with index + match

S

Sena

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

T. Valko

Try this:

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

Biff
 
T

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
 
S

Sena

Super it worked ..... many thanks !

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top