Change a range on cell value

Mcook13

New Member
Joined
May 14, 2019
Messages
43
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?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Instead of using the volatile function INDIRECT, why not just make your INDEX/MATCH cover enough rows for any amount of data you might expect?

eg =INDEX($B$2:$B$30000,MATCH(....... ,...$30000
 
Upvote 0
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Instead of using the volatile function INDIRECT, why not just make your INDEX/MATCH cover enough rows for any amount of data you might expect?

eg =INDEX($B$2:$B$30000,MATCH(....... ,...$30000

Agreed as I have done that. However I notice that when I do that, the formula takes longer to process. I use index match on many areas of my worksheet so I noticed when I decrease the range, it functions a little faster. Hence why I was looking to have the range adjustable off of a cell reference instead of changing the range in the formula on each and every cell.
 
Upvote 0
OK, I still wouldn't use INDIRECT as I think that would be even slower. You could try something like this to see how it goes.

20 08 23.xlsm
ABCDEFGHI
1Last Row5A#N/AZ5
2Q9Q9
3P8
4C1
5X2
6B3
7A6
8F5
9
10
Vary INDEX MATCH range
Cell Formulas
RangeFormula
F1:F2F1=INDEX(I$1:INDEX(I:I,B$1),MATCH(E1,H$1:INDEX(H:H,B$1),0))


You see that F1 results in an error because we are only looking at 5 rows, but if B1 is changed to, say, 10, we get ..

20 08 23.xlsm
ABCDEFGHI
1Last Row10A6Z5
2Q9Q9
3P8
4C1
5X2
6B3
7A6
8F5
9
10
Vary INDEX MATCH range
Cell Formulas
RangeFormula
F1:F2F1=INDEX(I$1:INDEX(I:I,B$1),MATCH(E1,H$1:INDEX(H:H,B$1),0))
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

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
Back
Top