Excel Index Dynamic List based on cell criteria

[ManUBlueJay]

I am looking to create a named range list to be used in Data Validation dropdown.

Contractor type choice is in L10.
Baes on this I want it to return all contractors who are Architects or whatever is chosen in L10.
I tried this which works for the single ones but not when the list is greater than 1
=INDEX(TableContractors,MATCH($L$10,TableContractors[Contractor Type],0),1,COUNTIF(TableContractors[Contractor Type],'$L$10))
The 2 col table is called TableContractors

Any Help would be appreciated

 

[djclements]

Try using OFFSET instead of INDEX:

=OFFSET(TableContractors[Contractor List],MATCH($L$10,TableContractors[Contractor Type],0)-1,0,COUNTIF(TableContractors[Contractor Type],$L$10))

If you don't want to use OFFSET, try INDEX:INDEX instead:

=INDEX(TableContractors,MATCH($L$10,TableContractors[Contractor Type],0),1):INDEX(TableContractors,MATCH($L$10,TableContractors[Contractor Type],0)+COUNTIF(TableContractors[Contractor Type],$L$10)-1,1)

Please note, structured Excel table references cannot be used directly within Data Validation; however, you can define custom names in Name Manager that reference the applicable table/columns, then reference those defined names in the Data Validation list source.

Also, it's important to note that your table in this example must always be sorted by Contractor Type in order to return the correct Contractor List.

 

[Peter_SSs]

@djclements
Don't both of your formula suggestions rely on the ability of the formula results to spill, noting that the OPs version indicates they do not have that feature?

Please note, structured Excel table references cannot be used directly within Data Validation; ..

Unless I am misunderstanding what you are saying, they can be used directly, with INDIRECT (if that is not a contradiction), in data validation without doing anything manually in Name Manager.
For example if DV list of Contractors was required

ManUBlueJay.xlsm
	K	L
1
2	Contractor List	Contractor Type
3	Cont 1	Architect
4	Cont 6	Consultancy
5	Cont 3	Architect
6	Cont 2	Contractor Type
7	Cont 5	Engineer
8
11
12	Contractor
Sheet3

Cells with Data Validation
Cell	Allow	Criteria
L12	List	=INDIRECT("TableContractors[Contractor List]")

@ManUBlueJay
I am not sure how you have set up your Contractor Type DV in L10 but if you are then looking to have a DV list of contractors based on the type chosen and you are still using Excel 2016 then here is one way (& sorting is not relevant). The helper column can be hidden.

ManUBlueJay.xlsm
	K	L	M	N
1				$N$2:$N$3
2	Contractor List	Contractor Type		Cont 1
3	Cont 1	Architect		Cont 3
4	Cont 6	Consultancy
5	Cont 3	Architect
6	Cont 2	Contractor Type
7	Cont 5	Engineer
8
9
10	Contractor Type	Architect
11
12	Contractor
13
Sheet1

Cell Formulas
Range	Formula
N1	N1	=ADDRESS(ROW(N2),COLUMN())&":"&ADDRESS(COUNTIF(N2:N7,"?*")+ROW(N2)-1,COLUMN())
N2:N7	N2	=IFERROR(INDEX(TableContractors[Contractor List],AGGREGATE(15,6,(ROW(TableContractors[Contractor List])-ROW(TableContractors[#Headers]))/(TableContractors[Contractor Type]=L$10),ROWS(N$2:N2))),"")

Cells with Data Validation
Cell	Allow	Criteria
L12	List	=INDIRECT($N$1)

 

[djclements]

Don't both of your formula suggestions rely on the ability of the formula results to spill, noting that the OPs version indicates they do not have that feature?

Unless I am misunderstanding what you are saying, they can be used directly, with INDIRECT (if that is not a contradiction), in data validation without doing anything manually in Name Manager.

Both of my suggested formulas will work in older versions of Excel (tested and confirmed with Excel 2010), so long as they're defined in Name Manager or used directly in Data Validation (without directly referencing the structured table names).

As you've pointed out, the INDIRECT function can be used as a workaround; however, I wouldn't recommend it because the Data Validation list source is limited to 255 total characters. With lengthy table/column names, the formula can easily end up exceeded the character limit. For example, the INDEX:INDEX variant would be 261 characters long when all of the necessary INDIRECT functions are added.

There's two ways I would recommend implementing my suggested formulas:

Option 1: If L10 is the only cell in the workbook where the Contractor Type is selected, then you can define a dynamic named range in Name Manger using the entire formula. For example, lstContractors could be defined as follows:

=OFFSET(TableContractors[Contractor List],MATCH(Sheet1!$L$10,TableContractors[Contractor Type],0)-1,0,COUNTIF(TableContractors[Contractor Type],Sheet1!$L$10))

NOTE: adjust the sheet name (Sheet1) as needed.

Then, use =lstContractors as the dependent Data Validation list source.

Option 2: If L10 is NOT the only parent cell in the workbook (e.g. if you need to have dependent data validation functionality for every row in a table), I would first define two shorter/concise names in Name Manager for the necessary table columns. For example, ctrNames and ctrTypes could be defined as =TableContractors[Contractor List] and =TableContractors[Contractor Type] respectively.

Then, the following formula could be used directly as the Data Validation list source:

=OFFSET(ctrNames,MATCH($L10,ctrTypes,0)-1,0,COUNTIF(ctrTypes,$L10))

This is what it looks like in Excel 2010:

I hope that clears things up. Cheers!

 

[Peter_SSs]

Both of my suggested formulas will work in older versions of Excel (tested and confirmed with Excel 2010), so long as they're defined in Name Manager

Ah, re-reading posts 1 & 2 together I realise that I had not interpreted your suggestion correctly.