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

EinarOSies

Board Regular
Joined
Feb 15, 2021
Messages
61
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
15,469
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0

EinarOSies

Board Regular
Joined
Feb 15, 2021
Messages
61
Office Version
  1. 2019
Platform
  1. Windows
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
 
Upvote 0

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
15,469
Office Version
  1. 365
Platform
  1. Windows
What condition? Some clarification would be good so that we know what you want rather than having to guess.
 
Upvote 0

EinarOSies

Board Regular
Joined
Feb 15, 2021
Messages
61
Office Version
  1. 2019
Platform
  1. Windows
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.
 
Upvote 0

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
15,469
Office Version
  1. 365
Platform
  1. Windows
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))
 
Upvote 0
Solution

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top