Vlookup, multiple criteria return multiple results per city

MarcBK

New Member
Joined
Apr 19, 2021
Messages
29
Office Version
  1. 2019
Platform
  1. Windows
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.

Can someone please help!
 

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
Joined
Oct 10, 2011
Messages
5,141
Office Version
  1. 365
Platform
  1. Windows
See the attached link on how to use INDEX and AGGREGATE functions to return multiple items.
 
Upvote 0

MarcBK

New Member
Joined
Apr 19, 2021
Messages
29
Office Version
  1. 2019
Platform
  1. Windows
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))))
 
Upvote 0

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
5,141
Office Version
  1. 365
Platform
  1. Windows
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))))
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top