Get multiple matches for the same lookup value

I have a data validation drop down list that displays a list of values in cell A2 from a range of cells. When you select a value from that range it displays another set value above A2 in A1 cell. For example ABC in the range of cells is associated with Los Angeles. When you select ABC in A2, it will display Los Angeles in A1 cell.

The problem is Los Angeles has multiple data in other range of cells, but only displays one cell data, and not rest of the data associated with ABC. What type of formula should I use for this example?

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to "Post Quick Reply" button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

---

Your current title is not acceptable, please edit...

Hey Frog, welcome to the forum. Could you please check the forum rules, particularly #1 about post titles, they should be descriptive of your problem not your proposed solution or a request for help. If you searched this forum looking for help for your issue would you search it using your current title? Everyone who comes here with a question needs help.

Second, for a better answer, can you upload a workbook showing some of what you have (doesn't have to be the whole thing) just a representative example and some expected results. That way we'll be better able to create a formula that might help you. Use go advanced under this quick reply window, then midway down click on manage attachments and a window will open that you can use to upload the workbook.
thanks.

Thank you for your reply, I have attached an example

I don't know why my title is not applicable, I need help with creating a formula for excel spread. I dont know what else to call it, but feel free to change the title.

Welcome to the Forum Frog213!

Because it's your first thread I'm going to update your title for you. For reference, your title should give us a hint to your question. You have posted in the "Excel Formulas & Functions" subforum, so virtually every post is asking for help to create a formula. We want to know what is unique about your question. We ask this for two reasons:

1. Members scan the list of threads to decide where they can help. The title helps us decide which questions we have the knowledge to solve.
2. After a question is answered, the title helps people with similar questions find it in a search.

Here is a solution. This requires an array formula. You may have to do a little searching on the details of that if you are not already familiar with it.

Thank you, its exacting what I am looking for. When I copied and pasted the formula and adjusted everything according to my data for some reason when I pick a code it doesn't update. It works fine in the file you sent me. The cell stays blank, and I have column count correct.

Please provide a workbook that shows the problem.

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

I need help with creating a formula for excel spreadsheet ...

So do 95% of all the others who ask for help here.