I am trying to do a pretty simple thing – I have two lists, Countries and Cities.
Each city has a country as a property in a separate column.
I want to list all the cities for each respective country in a separate column.
I can do that easily with something like that:
=FILTER(Cities!$A$1:$A; Cities!$B$1:$B = Countries!$A1)
And that works fine for the whole list of countries if I just stretch that formula down.
However, I need that list to be dynamic, so I could use ARRAYFORMULA, kinda like that:
=ARRAYFORMULA(FILTER(Cities!$A$1:$A; Cities!$B$1:$B = Countries!$A1:$A))
That doesn’t work obviously – and neither do OFFSET, CHOOSECOLUMN and even QUERY.
Is there a way to make that work?
>Solution :
Give a try on-
=MAP(TOCOL(Countries!$A1:A,1),LAMBDA(x,TOROW(FILTER(Cities!$A$1:$A; Cities!$B$1:$B =x))))