Vlookup or Index/Match Mulitple Solutions in a list form

[TheDapperDime]

Hello,

I've tried several formulas found on here and Google but can't seem to find a solution.

I am building a lookup tool where someone can select a market and it returns a list of the store numbers and names.

Example : Select mrkt -> Atlanta (from drop list)

Return: All the Atlanta store numbers and names

Here's the array I tried but it's not working:

=IFERROR(INDEX('Data'!B:B,SMALL(IF('Data'!Q$3:Q$1995="0001-ATLANTA",ROW('Data'!$Q$3:$Q$1995)),ROWS(Sheet2!B$5:B5))),"")

Please help I would greatly appreciate it.

[Norie]

Any chance you could upload a sample workbook?

Click on GO ADVANCED, scroll down and click Manage Attachments.

[Glenn Kennedy]

Looks OK to me, at a quick glance...

1. Are calculation options set to automatic?

2. Did the {} appear round the formula after CTrl-Shift-Enter?

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[Glenn Kennedy]

Ahh wait a minute... How does it get to "0001-ATLANTA" from a selection of "Atlanta" in the DD box??? There's something missing... INDIRECT("00001-"&$A$1) instead of "0001-ATLANTA"... where your DD is in A1. maybe...

[TheDapperDime]

Hi Team,

I just uploaded a test doc through Manage Attachment.

Thank you

[TheDapperDime]

Yes, {} appeared. I made sure to ctrl+shft+entr

Thx,
T

[TheDapperDime]

0001-Atlanta is the official name. I just wrote Atlanta for the post.

Thx,
T

[Glenn Kennedy]

Array formula in F7, copied across and down:

=IFERROR(INDEX('Test Data'!A:A,SMALL(IF('Test Data'!$C$2:$C$9='LookUp Tool '!$B$3,ROW('Test Data'!A$2:A$9)),ROWS(A$7:A7))),"")

[Glenn Kennedy]

or, since you have Excel 2016, an ordinary formula:

=IFERROR(INDEX('Test Data'!B:B,AGGREGATE(15,6,ROW('Test Data'!B$2:B$9)/('Test Data'!$C$2:$C$9='LookUp Tool '!$B$3),ROWS(B$7:B7))),"")

[Glenn Kennedy]

Re Atlanta... Whatever is in your DD has to match the data in column C of the raw data.

[TheDapperDime]

Thank you so much, Glenn. I really appreciate it!!!!!

-T

[TheDapperDime]

Ah ok. I see where I went wrong. Thanks so much!

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.