# Vlookup, multiple criteria return multiple results per city

#### MarcBK

##### New Member
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
Excel Formula:
``=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
Excel Formula:
``=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.

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### AhoyNC

##### Well-known Member
See the attached link on how to use INDEX and AGGREGATE functions to return multiple items.

#### MarcBK

##### New Member
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.

Excel Formula:
``=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

##### Well-known Member
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))))

Replies
6
Views
93
Replies
1
Views
64
Replies
5
Views
63
Replies
0
Views
218
Replies
5
Views
75

1,187,138
Messages
5,961,766
Members
438,563
Latest member
Ron Gluck

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back