Change a range on cell value

Mcook13

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

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
 

Mcook13

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,304
Members
414,224
Latest member
Crazy_FC

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