Forum Discussion
MicrosoftBuild a dynamic list based on other cell value
"Legacy" variant
=IFERROR(INDEX(cities[City],AGGREGATE(15,6,1/(LEFT(cities[City],LEN($C$2))=$C$2)*(ROW(cities[City])-ROW(cities[[#Headers],[City]])),ROW()-ROW($E$4))),"")(regular formula, just drag it down)
I'd modify the Filter as
=FILTER(cities[City],LEFT(cities[City],LEN($C$2))=$C$2,"No match")
It is possible. Depending on the layout of your workbook/spreadsheet you might want to do this in a very open area, since the number of rows that could be returned could vary a lot.
The FILTER function would work to accomplish what you're asking. If you can without disclosing any confidential information) upload a copy -- or a sample -- of your data, I (or somebody else) can surely create an example.
Microsoftmathetes Thanks for the reply. I'm attaching a sample of the data. Now the list on C5 is the complete list of cities. I'd need to have the list only displaying those cities that contains the string inserted on the cell C2. I'd appreciatte the support
Ezequiel
Here it is. FILTER worked (although I had to disable the data validation you'd put in that cell for some reason!!)
I've only recently learned how to use FILTER, and it's one really neat function. I'll be interested in your reaction after you try it with all 500 records. Right now it's set to only work with three character search functions. You could modify it with some sexy additional functions so that you could use variable length search strings, but let's start simple.
- ezequiel2235
mathetes Thanks! super helpful. Only one question to adapt your excel to mine. Should I enter the function as a array? How do I do it? Control+Shift+Enter?
Ezequiel
"Legacy" variant
=IFERROR(INDEX(cities[City],AGGREGATE(15,6,1/(LEFT(cities[City],LEN($C$2))=$C$2)*(ROW(cities[City])-ROW(cities[[#Headers],[City]])),ROW()-ROW($E$4))),"")(regular formula, just drag it down)
I'd modify the Filter as
=FILTER(cities[City],LEFT(cities[City],LEN($C$2))=$C$2,"No match")