Create a Dynamic Named Range using the INDEX Function in Excel

Create a dynamic named range in Excel using the INDEX function. A dynamic named range will automatically expand when new rows, or columns, are added to a list.
Learn more awesome Excel formulas - www.udemy.com/excel-formulas-...
This is possible using either the INDEX function or OFFSET. However OFFSET is a volatile function, meaning that it calculates even when not necessary. If your workbooks are large this will slow them down.
This video tutorial uses the INDEX function to create a dynamic named range. It shows a one-dimensional and then a two-dimensional range example.
The dynamic range is demonstrated in real-world scenarios. Firstly in a Data Validation drop down list, and then as a table array for the VLOOKUP function.
Find more great free tutorials at;
www.computergaga.com
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1

[@mohideenthassim7180]

Thanks Alan, great tutorial as always very clear and helpful

[@Sabrewave]

Great Tutorial! Clear, concise and beautifully executed. Couldn't ask for more and exactly what I needed. Thank you Alan.

[@Computergaga]

You're welcome. Thank you very much David.

[@rockguitarist8907]

Excellent explanation and walkthrough. Thank you so much for teaching this lesson to people like me who are trying to understand the complexities of Excel. :)

[@Computergaga]

Your welcome, thanks.

[@muhammadfathi3845]

Thanks, Awesome Video!!!

[@jackchan7532]

Many thanks. Much appreciate your work

[@Computergaga]

You're welcome, thank you.

[@Sam-cv6un]

You solved my problem. Thank you. :)

[@Computergaga]

You're very welcome Sam.

[@stoyanpetkov182]

thumbs up! Very helpful

[@Computergaga]

Thank you Stoyan.

[@janburckhardt]

Works well, thank you! However, when I try to use the named reference as an INDIRECT function it doesn't seem to work. I have a drop-down list in Column A and then a dependent drop-down list in Column B. Column B is an INDIRECT function of the value in Column A, which references the name of the range with the INDEX function, but this evaluates to an error. Please help!

[@dalskiBo]

Concise - thanks

[@Computergaga]

You're welcome! Thank you.

[@Leomunozguedes]

works, thanks!

[@Computergaga]

You're very welcome, Leonardo.

[@jackychawla5422]

nice and awesome please indirect function advance tutorial

[@Computergaga]

Thanks. Coming soon Jacky

[@abulaise07]

thanks for helping me. god bless u sir.

[@Computergaga]

Your welcome Abu

[@abulaise07]

Computergaga dear sir. i have some worksheet it's really needy to my daily work. if you send your email address I'll share the file. actually I don't no how my superior did the worksheet he not ready to teach me. pls show me how he did the worksheet.

[@Computergaga]

That's a lot of consultancy Abu.

[@abulaise07]

Dr. Rashid Baloch thank u so much. well how would I send the file to you sir.

[@scottthurgood241]

Is the Dynamic Named range specific to a Tab? Or how do we make it specific to one tab? What if we have Dynamic Ranges for multiple Tabs?

[@cd-ux9ot]

I heard named ranges with index are faster than offset. Only I recommend doing $A$1:$A$1000 as it's faster than counting the whole column

[@Computergaga]

Yes OFFSET is a volatile function and can slow a workbook down. As long as the range you use is big enough you can use what you wish.

[@mirrrvelll5164]

Great tutorial so far! Really decent explained! BUT, got one question, what in a case my range isn't whole column (not $A:$A), but like 5-10 rows (A1:A10..) within one column and how to add some of them but to be shown (auto updated) in data validation list??

[@Computergaga]

You can use the same technique and use that range instead of A:A.

[@mirrrvelll5164]

@@Computergaga Well, I tried exactly in your example and it didnt work. I added new row but no updates on data validation.

[@adoremm7052]

At the 7:00 mark you said that you are after the row number and the column number, What I dont understand is why did you use CountA($A:$A),column, first instead of ($1:$1) row. Can i use them interchangeably or did i go wrong somewhere?

[@Computergaga]

I used CountA($A:$A) first to retrieve the last row number in column A. You cannot use them interchangeably.

[@justinlim709]

hi, a question. why would you not use an excel table, since it expands and contracts automatically without formulas?

[@swamyjiki]

it crossed my mind too

[@MarkElDeiry]

If I want to name all of column A, is there a reason to avoid making the range equal to $A$1:$A$1048576?

[@kamranpouryavari9197]

Dear alan Please advice did this method worked with droupdown and indirect function i try but I didn't get any results

[@kamranpouryavari9197]

Dear alan Please advice if this methods worked with droupdown and indirect functions Best regards Kamran pouryavari

[@Computergaga]

Hi Kamran, yes I believe this works with INDIRECT and drop downs. You can also making a range dynamic by formatting it as a table and then naming that range.

[@teoxengineer]

Good technique!👏 However, everytime, we can not be able to use this formula. For example, if we erase a data inside of range, index formula will give us false result because of counting of rows. And, For the second example, if we insert a column for this table, vlookup will give us false result. But, Index():index() formula is so great and extraordinary function and so useful. Thank you

[@Computergaga]

Thank you, Emre. Yes, INDEX():INDEX() is awesome. For the first approach, instead of counting the rows, the ROWS fucntion could be used.

[@mirrrvelll5164]

So if I add additional column it will expand automatically without errors?

[@Computergaga]

Sure. The second is dynamic in both directions. Formatting your data as a table is another way.

[@stoyanpetkov182]

I was trying to use a range defined that way together with sum function, but it returned #VALUE. Basically the syntax is =SUM(MyRange), where MyRange is the range defined in the Name Manager. This works if the range is static, but it doesn't work if the range is defined with the INDEX function. Is there a way to make this work, or maybe to use another function to define the range?

[@Computergaga]

The INDEX function should not be a problem here. The #VALUE error is normally an indication of incorrect formatting especially with sum. Typically there is text in the range to be summed. When you said that it works without the named range, that confused me somewhat.

[@stoyanpetkov182]

@@Computergaga The named range actually works, but if it is defined as a static range, tried it both ways. If it is defined as static range, SUM function works, if it is defined as a dynamic - it doesn't. This makes me think, that the problem is not in the values of the cells. Works fine for validation list though. Edit: I tried this on a blank sheet, it works just fine. Something is wrong with my other document, I'll try to sort it out. Thanks for help anyway

[@charlesball9522]

I know this is a few years old but let's hope for the best. I used this formula and it was successful up to a point. I have 48 items in my range but it only finds 46, if i add one it will find the 47th one but it won't find the 48th and 49th. in your example it would be like your range is France through Venezuela, but it only showed you France through Spain, then when you added Italy it would show you France through UK.

[@charlesball9522]

I discovered the problem. there were two blank rows above the first bit of data, (A1 and A2 had column headers, A3&4 were blank, and A5 was the first data in the range). i used =$A$5:INDEX($A:$A,COUNTA($A:$A)).

[@Computergaga]

Well done Charles.

[@Dark_Jester89]

How could you go about if you needed a City range, based on the country. B2: France countries, B3: Germany countries, etc, and if the A:Column moves, the b:Columb accommodates to it. Thanks in advance!

[@Computergaga]

There are a few different techniques depending on what you need the list of cities for. You could use something similar to what I show in this video - kzbin.info/www/bejne/iGbPnodjbNJrl7M Here I get VLOOKUP to look in a different range depending on a cell selection much like your question on city range dependent upon country selection. You just may not need the VLOOKUP. You can also check out this situation where I have a dependent drop down list. The second list is dependent upon selection from the first - kzbin.info/www/bejne/r6rGmYuGrZeai7s

[@charliefasurf1000]

Hey, it doesnt work in excel 2016. Data validation "the source must be a delimited list, or a reference to a single row or column"

[@Computergaga]

Check the video again to try and spot the problem. The video was done in 2016 but will work in any version.

[@felixstiefel9443]

Would the number of columns of the new dynamic range not be 5, instead of 3?

[@Computergaga]

This is true. Does not affect the VLOOKUP in the example, but you are correct.

[@felixstiefel9443]

@@Computergaga Thanks for replying! Great video.

[@mohammedsalwan382]

What about offset,index and counta function is that work?

[@Computergaga]

You can use OFFSET instead of INDEX if you wish.

[@mohammedsalwan382]

@@Computergaga Sir, OFFSET and COUNTA function is the best combination for dynamic range.

[@Computergaga]

@@mohammedsalwan382 👍

[@atherbeg2177]

When I use this in SUM & If array, I get N/A error. Why?

[@Computergaga]

I would need to see more detail. SUM can absolutely be used on a range returned by INDEX.