Vlookup, multiple criteria return multiple results per city

[MarcBK]

Hello all,

I have a sheet with two drop down tabs in sheet 1 cells A2 and A3

A2 = Region
A3 = City

the following formula in cell D2

=IFERROR(VLOOKUP($A$2,'Main cities'!$1:$1048576,5,FALSE),"")

to return the address of the particular place found when the search criteria of city or region is entered.

So I am trying to lookup the data found in sheet 3 'Main cities'!1:1048576 and return addresses based on city or region codes.

In sheet 3 City is in column A and region is in column B and address (result I want to return in sheet 1 D2 - D35) is found in column E of sheet 3

I have been trying around with differing formulas such as

=IFERROR(VLOOKUP($A$2,'Main cities'!$1:$1048576,5,FALSE),"")

and other xlookup types etc but as much as this formula works to return the first address from column D in my main cities sheet, when I pull this formula down it just continues to pull the first address only from the main city sheet even though there are multiple addresses for each city.

Can someone please help!

 

[AhoyNC]

See the attached link on how to use INDEX and AGGREGATE functions to return multiple items.

 

[MarcBK]

See the attached link on how to use INDEX and AGGREGATE functions to return multiple items.

Thanks but I am unable to get this to work.

=If(ROWS(D$2:D33)>$C$2,"",INDEX('Main cities'!E:E,AGGREGATE(14,6,(ROW('Main cities'!A:A)-ROW(Table1[@City])+1/('Main cities'!A:A=Search!B2,ROWS(D$2:D33))))

 

[AhoyNC]

You have your parentheses in the wrong places. Also, it is not a good idea to reference whole columns (which may be an issue here, not sure). If you actual have your data set up as a table then the formula should expand as data is entered into the table. Otherwise if your data would say never go past row 50000 then use something like A:1 to A:50000.
Maybe try:

If(ROWS(D$2:D33)>$C$2,"",INDEX('Main cities'!E:E,AGGREGATE(14,6,(ROW('Main cities'!A:A)-ROW(Table1[@City])+1)/('Main cities'!A:A=Search!B2),ROWS(D$2:D33))))