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")
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")Yes. That addition of LEN was what I had in mind to make it more widely functional, but I was also trying to begin "simple". 🙂
I know I did NOT enter it with any special flourishes.
Part of the magic of FILTER (that I'm just discovering) is that it takes care of filling the adjacent cells down and to the right, as needed (based on the dimensions of the array you are filtering). I have one of my own where I enter the FILTER function in one cell and it fills the cells down as many rows as needed, and 23 columns to the right.
You will need to adjust the array that it searches with the full dimensions (or the name of the array) of your 5,000 cities. If you have country and other data as separate adjacent cells in that 5,000 row table, you might want to retrieve there data too, but just make sure you provide sufficient space for it to "spill" as much as needed.