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

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