How can I use INDEX&MATCH to populate dropdown list with multiple values?

Hello all,

this thread is a follow-up on a previous thread in which I used INDEX&MATCH to find a corresponding value to a search string.

I would now like to use this principle to fill a dropdown list.

I attached a table.

In it you will find a search string "A". Then a table with multiple rows where "A" is repeatedly found but with different values in C. I would now like to create a dropdown list where for each A found the corresponding number is listed and can be selected.

Could someone help me with this please?

Thank you all very much

Alex

EDIT: I cannot attach any files. Is this a forum limitation because I am new? Tried Chrome and IE.

Here is a picture instead. Sorry about that

Capture.PNG

@davsth:
Not sure I understand. You posted what I posted. (see the formula at the top of the image).

I am trying to adjust this to return all values separated by ";" so I can use the formula in a dropdown list.

My, and, if I am not missing something, your formula will only return the first result it finds. Not all of them.


I did some more digging and I think that maybe the SMALL() function is needed, but I admittedly do not know how to get it to work. Especially not when trying to return all the values separated with ";" so the dropdown can use it.

If you choose A in cell A3, how will Excel know which "A" you want to look at in your list in B3:B12? Are you looking for a dropdown list in A4 containing 1,4,5?
You should be able to attach a file. You'll need to do that in "Go Advanced" Review the yellow banner at the top of the page for instructions.

To create your list to use for dropdown, in G1 (for example) copied down

Formula:

Please Login or Register  to view this content.

Originally Posted by ChemistB

Are you looking for a dropdown list in A4 containing 1,4,5?

That is exactly what I want.

I attached a file. Sorry, I tried the button next to the emoticon button instead of the "Manage attachments" below the text field.

So in the attached example I would like to have a dropdown list in F4 which shows the values of 1,4,5. But not by me adding them manually but rather using a formula which will update whenever a new combination of "A" and value in row C occours.

So should I add B3="A" and C3="100", then the dropdown would contain 1,4,5,100.

My INDEX&MATCH formula is able to find the first occurence of "A" but does not create a ";" separated list of all of them. And I think that would be needed to use the formula for the dropdown.

EDIT:

Originally Posted by ChemistB

To create your list to use for dropdown, in G1 (for example) copied down

Formula:

Please Login or Register  to view this content.

Two questions:
1. Is it possible to not limit the search areas but use entire columns? Because the search area will grow over time when people add more data in columns B and C.
2. This does not create one list but creates individual cell values. Is it possible to not need a copy & paste list but rather a one-liner that creates a ";" separated list which can be used in data validation as the list formula? I do not want to create a list somewhere and then refer the data validation dropdown to an external list.
Do you know what I mean?

=INDEX&MATCH/AGGREGATE-Formula should return "1;4;5" in "data validation" - "settings" - "Formula" or "list"

I do not believe that you can use the aggregate (or the Small function or any other way) to bypass the step where you pull your list into a contiguous range. You can place that list on another sheet or hide that column but it must exist for data validation.

Using an entire column will slow down your workbook calculations. There are over 1 million rows in a sheet. Perhaps an intermediate number that you know they will never go over (i.e. 50, 500, 5000, even 100000)

Formula:

Please Login or Register  to view this content.

In your sheet, i put the formula in Column I (which can be hid) from I1 to I20
I named this range dynamically in Name Manager so that only actual numbers show.

Formula:

Please Login or Register  to view this content.

If your values aren't numbers, then maybe

Formula:

Please Login or Register  to view this content.

Then Data Validation > List
=MyDropDown

My formula didn't use CSE so not sure why you had to adjust to avoid CSE.
I would move your created list down 1 row and in the first row, enter "Please Select". Then your formula for your dropdown will be

Formula:

Please Login or Register  to view this content.

Does that make sense? If not, can you upload an updated example?

Ref Post #8
Single formula can be used for Named Range

=$I$1:INDEX($I$1:$I$20,SUM(COUNT($I$1:$I$20),COUNTIF($I$1:$I$20,"*?")))

Using the file attached to post #6 a possible modification to the formula could be:

Formula:

Please Login or Register  to view this content.

Note that commas should convert to semi-colons in the attached file.
Let us know if you have any questions.