INDEX & MATCH to find Y value in Table for Validation List

Hi All,

I'm trying to set up a dependent validation list that pulls it's data from the Y values of a table (first column) the list will be rather large, and there will be a number of initial values (that this list is dependent on). I am hoping to avoid having to use a structure like that shown in Table 1 below, and use one like that shown in Table 2 where TRUE indicates which result relates to each particular input. I have the following non volatile* formula (as a named 'range') which works with Table 1 for the validation list:

=INDEX(B3:B7,MATCH(C8,A3:A7,0),1):INDEX(B3:B7,MATCH(C8,A3:A7,1),1)

Table 1

avenue	vlresult
av1	result1
av1	result3
av2	result2
av2	result3
av3	result1
av4	result1
av4	result4
av5	result3
av6	result2

Table 2

	av1	av2	av3	av4	av5	av6
result1	TRUE		TRUE	TRUE
result2		TRUE				TRUE
result3	TRUE	TRUE			TRUE
result4				TRUE

I have tried to construct the formula myself but I've run into issues with MATCH only accepting a 1D array. Had thought to get around this by identifying the column first then conducting a standard INDEX MATCH on this 1D array, but alas I haven't had much luck with getting it to work...

Any and all suggestions will be greatly appreciated, however I hope to keep the formula non volatile.

I've attached a clean workbook with the above sample data and validation list: ExcelForum INDEX MATCH Validation.xlsx

Cheers,
DJ

* At least I don't think it's volatile!

It's not volatile; but I do not think it will work; copied and pasted into the name drop-down confirms this; I get an error message telling me that it is not a valid reference.

Not clear on what you are trying to do; so I'll hazard some guesses.

Are you trying to build a two-dimensional range from columns A and B? If so, are you wanting to populate a drop-down from that? If that is the case it cannot be done.

On the other hand if you are trying to populate a second drop down based upon the selection in your first drop-down that can be done. If that is the case there are a new set of questions.

Hi Jitendra,

Thank you for your response, unfortunately that does not provide the end result I'm after.

To clarify, I hope to replace Table 1 with Table 2 and still have the validation list behave how it currently behaves.

-DJ

Are each of the items in D3:D6 going to be drop-downs......also E2:J2 such that this formula in E3:J6 will generate variations of this such as is in your workbook?

Formula:

=IF(SUMPRODUCT(($D3=$B$3:$B$11)*(E$2=$A$3:$A$11)),TRUE,"")

Row\Col	D	E	F	G	H	I	J
1	Table 2
2		av1	av2	av3	av4	av5	av6
3	result1	TRUE		TRUE	TRUE
4	result2		TRUE				TRUE
5	result3	TRUE	TRUE			TRUE
6	result4				TRUE

or this variation?

Row\Col	D	E	F	G	H	I	J
1	Table 2
2		av1	av2	av3	av4	av5	av6
3	result1	TRUE		TRUE	TRUE
4	result2		TRUE				TRUE
5	result1	TRUE		TRUE	TRUE
6	result4				TRUE