Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Multiple Values from a variable table size
I Have the follwing arrray formula:
{=INDEX(Mes!$B:$B,SMALL(IF(Mes!$A$1:$A$13400=$M$1, ROW($1:$13400)),ROW(1:1)))} It works OK if the rows are always 13400 but if it grows or shrinks it wont work, how can I change the formula so that if the amount of rows changes it still gives me correct results? Thank You for your help. -- Gabriel Camarena R. Delphi Tijuana IT Support |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Multiple Values from a variable table size
It works OK if the rows are always 13400 but if it
grows or shrinks it wont work If the table shrinks it'll still work but if the table grows it won't work without changing the formula to reflect the new range size. Use a dynamic range. But, because your range is pretty big for this type of formula it'll slow down calculation. Assuming there are no empty cells within the used range Mes!A:A Goto InsertNameDefine Name: rng Refers to: =Mes!$A$1:INDEX(Mes!$A:$A,COUNTA(Mes!$A:$A)) Then, array entered: =INDEX(Mes!$B:$B,SMALL(IF(rng=$M$1,ROW(rng)),ROWS( A$1:A1))) -- Biff Microsoft Excel MVP "GCRDelphi" wrote in message ... I Have the follwing arrray formula: {=INDEX(Mes!$B:$B,SMALL(IF(Mes!$A$1:$A$13400=$M$1, ROW($1:$13400)),ROW(1:1)))} It works OK if the rows are always 13400 but if it grows or shrinks it wont work, how can I change the formula so that if the amount of rows changes it still gives me correct results? Thank You for your help. -- Gabriel Camarena R. Delphi Tijuana IT Support |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple lookup values and adding multiple rates across together | Excel Worksheet Functions | |||
multiple entries per cell to save on table size? | Excel Worksheet Functions | |||
Variable Table Array in Lookup Function | Excel Worksheet Functions | |||
Lookup in Multiple Columns, Return Multiple Values | Excel Worksheet Functions | |||
Excel multiple variable data table | Excel Discussion (Misc queries) |