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}.

A	B	C	D
Joe	apple	[user enters column A value here]	[validation list with filtered column B values here--depending on user input on C1]
Joe	corn
Joe	pear
Mary	apple
Mary	corn

<tbody>
</tbody>

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!