Lookup Multiple Values from a variable table size

  • Thread starter Thread starter GCRDelphi
  • Start date Start date
G

GCRDelphi

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.
 
It works OK if the rows are always 13400 but if it
grows or shrinks it wont work
Click to expand...

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 Insert>Name>Define
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)))
 
Back
Top