Hello Excel Wizards,
I have an Index March formula and I want to have the range be adjustable. I thought I had it, but now I can't get the formula to work.
I have this first part of my Index Match formula so I'm pretty sure you would understand what I'm looking to have it do:
=INDEX($B$2:$B$1000,MATCH(.......
So that I could expand the range as needed since more data would added to column B, I was trying to have the range be expandable by having the second part of the range referenece a cell so I can change it from 1000 to 1500 or 2000. So I added INDIRECT with CONCATENATso the formula reads:
=INDEX($B$2:INDIRECT(CONCATENATE("B",$G$2))MATCH(.......
However when I run the formula it does CONCATENATE first which returns an error and the rest of the formula fails.
Any and all help is appreciated on how to make this work. Automatically adjusting range is also an alternate, but not sure on how to do that as well.
Would doing XLOOKUP rather than INDEX MATCH work better to have an adjusting range?
I have an Index March formula and I want to have the range be adjustable. I thought I had it, but now I can't get the formula to work.
I have this first part of my Index Match formula so I'm pretty sure you would understand what I'm looking to have it do:
=INDEX($B$2:$B$1000,MATCH(.......
So that I could expand the range as needed since more data would added to column B, I was trying to have the range be expandable by having the second part of the range referenece a cell so I can change it from 1000 to 1500 or 2000. So I added INDIRECT with CONCATENATso the formula reads:
=INDEX($B$2:INDIRECT(CONCATENATE("B",$G$2))MATCH(.......
However when I run the formula it does CONCATENATE first which returns an error and the rest of the formula fails.
Any and all help is appreciated on how to make this work. Automatically adjusting range is also an alternate, but not sure on how to do that as well.
Would doing XLOOKUP rather than INDEX MATCH work better to have an adjusting range?