Validation list with array formula (index/ match)?

[vbeee]

Hi All,

I am trying to achieve the following, using the table below as expample:

Set up in D1 a validation list (dropdown) that pulls the right values from column B, based on the filter on A (from user choice in C1).

E.g. if user inputs "Joe" in C1, then the validation list in D1 should have {apple, corn, pear}.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]apple[/TD]
[TD][user enters column A value here][/TD]
[TD][validation list with filtered column B values here--depending on user input on C1][/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]corn[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]pear[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]apple[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]corn[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Question: what should be the formula for the validation list in D1, taking into account that it seems that array formulas do not work there (i.e. index/match)

Finally, I do not want to have separate columns or sheets to precalculate stuff. Also, order is not significant.

I tried I am busting my head but cannot make it work...

Thanks!

 

[vbeee]

Forgot to mention that the solution is for Excel 2010 (no 2013!!!).

 

[RoryA]

Try:

=OFFSET($B$1,MATCH($C$1,$A:$A,0)-1,0,COUNTIF($A:$A,$C$1),1)

Adjust to match your ranges/sheet names.

 

[vbeee]

Thanks a ton Rory! This was really neat and helpful! Greatly appreciated!