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

[EinarOSies]

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

 

[jasonb75]

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]

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]

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

 

[EinarOSies]

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]

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.

=INDEX($B:$H,MATCH($J13,$A:$A,0),MATCH($L$1,$B$1:$H$1,0))

 

[EinarOSies]

Thanks I really appreciate it