Help adding dynamic named range

[pwl2706]

Hi,

I have atable which can grow in rows, and the columns will stay the same at 10 columns

I want to add a dynamic named range called BenefitsData.

I tried this:

OFFSET(B7,0,0,COUNTA(B7:$K$200)

and get no error but when I try to use it in my INDEX formula as the array argument I get #VALUE or #NAME error

Here is my INDEX formula:

INDEX(B7:K21,MATCH(B3,Employees,0),MATCH(B2,Benefits,0))

B7:K21 should be the dynamic part, whether in a named range or not

 

[Jon von der Heyden]

Hi

What is in column B? Numbers, text or both?

 

[pwl2706]

columns B to K will be numbers only (Currency, salaries etc)

 

[Jon von der Heyden]

Try;

=INDEX(B:B,7,1):INDEX(K:K,MATCH(9.99999999999999E+307,B:B,1),1)

 

[pwl2706]

thankyou, that works great -

am I right in my understanding that by using the largest number possible with approximate match it gets the last cell?