| Home |
| Search |
| Today's Posts |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index and Match Array formula
Thanks for the clarification.
Biff wrote: Array formulas don't like whole columns. Ir depends on how the formula is structured and what function is calling the entire column: =INDEX(A:A,MATCH(1,(B1:B20=100)*(C1:C20=1000),0)) Index is using the entire column. =INDEX(A:A,MATCH(1,(B1:B20=MAX(B:B))*(C1:C20=MAX(C :C)),0)) Max is using the entire column. Also, (although this isn't a real good example), entire columns can be used in nested functions within Sumproduct: =SUMPRODUCT(SUMIF(A:A,100,B:B)) Biff "Dave Peterson" wrote in message ... Array formulas don't like whole columns. Graham Haughs wrote: Can someone tell me why the first array formula returns the correct value, and the second one returns #NUM. The array size increases so I don't want to limit the size as the first formula does, and thought the second formula would allow this. Once again I would really value some help. {=INDEX(A2:A87,MATCH(1,(G93=C2:C87)*(G94=B2:B87),0 ))} {=INDEX(A:A,MATCH(1,(G93=C:C)*(G94=B:B),0))} Kind regards, Graham Haughs Turriff Scotland -- Dave Peterson -- Dave Peterson |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Array index, match problem | Excel Worksheet Functions | |||
| index match formula | Excel Worksheet Functions | |||
| How to add in an array formula if iisna index match | Excel Worksheet Functions | |||
| need help with Index, Match and Countif in the same complicated formula | Excel Discussion (Misc queries) | |||
| Vlookup, Index & Match | Excel Worksheet Functions | |||
Threaded Mode
