Lookup which has the ability to return more than 1 value

PeggyLang

PeggyLang

I have a worksheet which lists store locations. Each store has a 'Store #'. When speaking with the end user today I learned that they need a Store Lookup solution, however, they usually don't know the 'Store #'. They would like to lookup Store info based on the city the store is located in. The problem is there are numerous city's across the country where there are more than 1 store in the market. I.e., if I lookup store data based on city and 'Atlanta' is input, there happens to be 5 locations in Atlanta.

What I would like to happen is if 'Atlanta' is selected in the Lookup Sheet, specifics for each of the 5 locations in Atlanta are returned.

Would look something like the below.

Thoughts? Can this be accomplished?

Thanks in advance for any help.

Spoonhead

You could use a combination of filters and lookups to achieve this, if I'm following you.

1. Create a Lookup Sheet:
   • Add a new sheet where users can input the city name.
   • Include a column named "City Lookup" for entering the city.
2. Create a Report:
   • Go to the Report Builder and create a new report.
   • Set the source sheet to your main store locations sheet.
3. Set Filter Criteria:
   • In the report, add a filter where the city column matches the value in the "City Lookup" column of the Lookup Sheet.
   • This will dynamically filter the report based on the city entered.
4. Display Results:
   • Ensure the report includes columns like Store #, Address, ST, ZIP, and Phone #.
   • Save the report. It will now update automatically to show all stores in the city entered in the Lookup Sheet.

Example:

1. User enters "Atlanta" in the "City Lookup" column of the Lookup Sheet.
2. The report automatically filters and displays all stores in Atlanta, showing details for each location.

This setup allows users to look up store information based on the city name, even if there are multiple stores in the same city.