Forum Discussion
ezequiel2235
Microsoft
MicrosoftBuild a dynamic list based on other cell value
Hi,
I have a list of 5000 records with names of cities (list 1). The user needs to select one but given the size of the list it is difficult to find it. Is it possible to build something that c...
"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")
ezequiel2235Microsoft
Microsoftmathetes 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". 🙂
