Thread: Using INDEX and MATCH to find last used cells with defined names

I have validation lists that pull data from defined names, then I also have a data table that has data on these items. When I drill down with the validation lists, I wanted to populate additional fields with data from this other worksheet. Specifically I wanted to dynamically grab the last two used cells from the data table (slope & intercept).

I am running into trouble trying to get the correct formula to do so:

Code:

=OFFSET(INDEX(Items,MATCH($C2,Items,0)),0,COUNTA("Data!"&MATCH($C2,Items,0)+1&":"&MATCH($C2,Items,0)+1))

This does not appear to work and I have been playing around with variations in hope to figure it out with no luck. Slope would be offset -1 from the last used column in the data range, and intercept will be the last used column.

See attached for sample workbook. Thanks in advance.

Do you mean?

=OFFSET(Items,MATCH(C2,Items,0)-1,11,1,1) for Slppe

and =OFFSET(Items,MATCH(C2,Items,0)-1,12,1,1) for Intercept

You probably can just do this instead:

=OFFSET(Items,MATCH(C2,Items,0)-1,COUNTA(Data!$1:$1)-3,1,1)

and

=OFFSET(Items,MATCH(C2,Items,0)-1,COUNTA(Data!$1:$1)-2,1,1)

this offsets a total number of columns equal to the number of columns in row 1 minus 3 and minus 2, respectively.

Thanks again, that worked great.