Value in one cell determines another cell vice versa using drop down list

[Shanthan]

[TABLE="class: grid, width: 151, align: left"]
<tbody>[TR]
[TD]Fruits[/TD]
[TD]Colour[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]Mangoes[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]Bananas[/TD]
[TD]Yellow[/TD]
[/TR]
</tbody>[/TABLE]








[TABLE="class: grid, width: 151"]
<tbody>[TR]
[TD]Fruits[/TD]
[TD]Colour[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

How can I use data validation drop down list to do the following: When the user picks a fruit in the second table (using a data validation list pulled from the first table), the corresponding colour should appear in the colour column. If the user then selects a different colour in the colour column, the fruit column should change accordingly.

E.g., if I pick Apples, then the adjacent cell should show Red. If I change Red to Yellow, the cell under Fruits should show Bananas.

I tried INDEX MATCH formula, but I could only make it work one way.

 

[seekerarcane]

Try this one, but reversal of dependent drop down value seems impossible, may be some VBA code could help.

You can create dependent drop down list using the below one.


http://www.contextures.com/xlDataVal02.html