# Combining the last non-filled cell in a range with Index Match

#### EinarOSies

##### Board Regular
Hi,
I have an index match fomula, that is "=INDEX(\$B\$3:H96,MATCH(\$J13,\$A\$3:A96,0),MATCH(\$L\$1,\$B\$1:\$H\$1,0))"

Another formula that is ="ADDRESS(MATCH(2,1/(A:A<>""""),1),COLUMN(A:A))" which finds the last non filled cell address in column A and "=ADDRESS(MATCH(2,1/(H:H<>""""),1),COLUMN(H:H))" which finds the last non filled cell address in column H.

My challenge is how can I combine these functions together without getting an error.

Thanks

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### jasonb75

##### Well-known Member
What is the purpose behind wanting to combine them? If it its only to make the end of the range (row 96 in your first formula) dynamic then there are many better ways that you could do it.

Dynamic named ranges probably being the best one.

#### EinarOSies

##### Board Regular
What is the purpose behind wanting to combine them? If it its only to make the end of the range (row 96 in your first formula) dynamic then there are many better ways that you could do it.

Dynamic named ranges probably being the best one.
Please then what formula can satisfy that condition

#### jasonb75

##### Well-known Member
What condition? Some clarification would be good so that we know what you want rather than having to guess.

#### EinarOSies

##### Board Regular
What I want to achieve is that looking at the Index Match function "=INDEX(\$B\$3:H96,MATCH(\$J13,\$A\$3:A96,0),MATCH(\$L\$1,\$B\$1:\$H\$1,0))" it lookups from the range B3 to H96.

The reason why I am looking for the last non-blank cell address in column H with the formula "=ADDRESS(MATCH(2,1/(H:H<>""""),1),COLUMN(H:H))" is that if the reference table grows and it goes outside the range boundary, let say after H97 with finding the address of address will accommodate the growth of the table so that the INDEX_MATCH formula will be dynamic to the lookup.

In this same way, when the range grows column A will also grow so finding the last non-blank cell address in column A will be ="ADDRESS(MATCH(2,1/(A:A<>""""),1),COLUMN(A:A))".

Please I hope this summary helps.

#### jasonb75

##### Well-known Member
Just take the row numbers out of the first formula, it's not going to be as efficient as dynamic ranges but will be a better than what you are attempting.

Excel Formula:
``=INDEX(\$B:\$H,MATCH(\$J13,\$A:\$A,0),MATCH(\$L\$1,\$B\$1:\$H\$1,0))``

#### EinarOSies

##### Board Regular
Thanks I really appreciate it

Replies
6
Views
174
Replies
3
Views
494
Replies
9
Views
309
Replies
10
Views
258
Replies
3
Views
102

1,186,605
Messages
5,958,770
Members
438,375
Latest member
mage

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back