Create Dynamic Drop-Down Lists Based on Numbers Using INDEX (without MATCH) in Excel

Discover a unique perspective on the INDEX function - which is often paired with the MATCH function in Microsoft Excel - for data retrieval and complex lookups.

But there's more to INDEX than meets the eye:
- Exploring Lists: Learn how to use the INDEX function to navigate through different lists based on user selections.
- Simplifying Arguments: Get tips on choosing the right array and arguments for efficient data mapping.
- Dynamic Functionality: See how changing one value dynamically updates your data.
- Alternative Approaches: Uncover another method using references and area numbers for even more flexibility.
- Data Validation Techniques: Find out how to create a dynamic dropdown menu using named ranges and data validation.
- Performance Insights: Understand why INDEX is often a better choice than OFFSET, especially in large spreadsheets.

This video shows another side of the INDEX function. It uses INDEX to create dynamic lists based on criteria. It also uses the second argument or way of writing of the INDEX function which uses references as well as area numbers.

The INDEX function is then used to create dynamic drop down lists that change based on selected criteria. Name manger is used to keep the formula required for the data validation list. The given name is then directly used inside the data validation.

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[jamesperry]

I watched the first 2mins 30secs and the way u use the INDEX + DV function is so magical. I also like the Highlight + F2 + Ctrl + Enter. Thanks Leila.

[emeister]

that's sooo great and useful!!! i never give feedback or comments in youtube, but i gotta give you a thumbs up on this!!!!

[sureshJayaraman]

7:15 is exactly what I needed! You are the best!

[jgvlc]

Thank you very much, I love your solution. The content in your channel it´s fantastic. Thank´s.

[akmzaman]

Hello Leila, it's simply awesome. I had no clue that Index formula can be used like this. Your lacture is also very clear and lucid. May god bless you. Very best regards.

[abdulfattahalhazmi]

WOW Leila, Well done!
I am very impressed :)
Great channel

[rekhamanwani]

U R SUCH AN SO MUCH KNOWLEDGE AND EASY TO LEARN.KEEP IT UP

[davidroper]

I have been trying to work this out for a couple of weeks. I feel quite embarrassed now that, I just didn’t initially see how this would work out and how Leila subsequently worked it out. But, I’m now quite edified and thankful for her lecture, thank you! Also the F2 to edit mode and the Ctrl + Shift was a great shortcut. I’m now able to help out a colleague in the office by adding the different list to generate different charts. I’ve learned a lot with this short lecture.

[AliAli-bmvz]

Fantastic i love ❤️ it .many thanks for u

[gopalakrishnarao]

Very useful and informative. Please upload at least one videos daily for all the Excel lovers/addicts/users. I am one of your fan. You are experimenting with Excel formulas and explaining beautifully, in all the ways. Hats . Please help to the Excel users by starting the series VBA Basics.

[hniali]

Sweet and great words will never ends with your vedios
Thank you very much

[vkz_world]

Hi @Leila, Great to learn from your videos. But is there way that a cell also represents/ indicate corresponding values for the apps selected?

[stephaniemorgan]

Thank you very much for your fabulous videos! I have applied your Index (reference) formula in exactly the way you demostrated for a drop down list. Everything works perfectly up until the point where I select an element from the list and then swap out the locked area number but the previously selected item from the drop down list doesn't disappear. So end up with an element selected that is actually not possible to select. Do you have any suggestions about how to solve this please?

[dhavalshah]

Leila You are the great again you are the queen of Excel, Thanks for this video.

[discob]

can this be used for a staff roster, list the dates, which then determines the staff working based on COUNTA in the dates

[Tanushree]

Hi, I have a question relating to the "Array". Its the area where my answer is .So here when we key in the App Column number then the answer against it is present in the whole spread sheet .So why did we not select the whole MAP nstead we selected only the first row.

[jsjjb]

I have 2 lists of numbers 1-40. The first list I will make as a drop down in my worksheet. The second list is a direct correspondent to the first list of numbers. For instance number 1 (1st list) = 34 (2nd list). So if I select 1 from drop down list in worksheet I want to have 34 populate in the next column over. Would like help on how to do this with 40 numbers.

[shau]

Thank you Leila. I did not know INDEX((A43, B43, C43).... Thanks a lot

[SanjayKumar-yxgc]

Excellent examples of Index formula. Many-2 thanks Leila. I want to at least one example of combination of index & offset function.

[kyfparacha]

how to make this working if cell G41 is not used as a fix cell. i mean what if there is a table and index function needs to work one row down with each no entry.