| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula with index + match
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 -- Cheers ! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array formula with index + match
Try this:
=SUMPRODUCT(--(ISNUMBER(MATCH(I39:N39,CODE,0))),INDEX(TB,,MATCH( F20,A,0)) Biff "Sena" wrote in message ... 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 -- Cheers ! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array formula with index + match
What does "--" mean ? and how is it entered in formulae ?
-- Cheers ! "T. Valko" wrote: Try this: =SUMPRODUCT(--(ISNUMBER(MATCH(I39:N39,CODE,0))),INDEX(TB,,MATCH( F20,A,0)) Biff "Sena" wrote in message ... 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 -- Cheers ! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array formula with index + match
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 "Sena" wrote in message ... What does "--" mean ? and how is it entered in formulae ? -- Cheers ! "T. Valko" wrote: Try this: =SUMPRODUCT(--(ISNUMBER(MATCH(I39:N39,CODE,0))),INDEX(TB,,MATCH( F20,A,0)) Biff "Sena" wrote in message ... 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 -- Cheers ! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array formula with index + match
Super it worked ..... many thanks !
One more clarification - why there is no value in index formulae for row number ? -- Cheers ! "T. Valko" wrote: 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 "Sena" wrote in message ... What does "--" mean ? and how is it entered in formulae ? -- Cheers ! "T. Valko" wrote: Try this: =SUMPRODUCT(--(ISNUMBER(MATCH(I39:N39,CODE,0))),INDEX(TB,,MATCH( F20,A,0)) Biff "Sena" wrote in message ... 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 -- Cheers ! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array formula with index + match
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 "Sena" wrote in message ... Super it worked ..... many thanks ! One more clarification - why there is no value in index formulae for row number ? -- Cheers ! "T. Valko" wrote: 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 "Sena" wrote in message ... What does "--" mean ? and how is it entered in formulae ? -- Cheers ! "T. Valko" wrote: Try this: =SUMPRODUCT(--(ISNUMBER(MATCH(I39:N39,CODE,0))),INDEX(TB,,MATCH( F20,A,0)) Biff "Sena" wrote in message ... 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 -- Cheers ! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help with an Index Match Array | Excel Worksheet Functions | |||
| index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
| Index and Match Array formula | Excel Worksheet Functions | |||
| Looking for formula index/match-type that returns an array | Excel Worksheet Functions | |||
| How to add in an array formula if iisna index match | Excel Worksheet Functions | |||
Linear Mode
