Formula to show an adjacent cell to the one picked in a data validation list

Hi All,

I have a source range as shown in the attachment below.

Data source.JPG

On another sheet I have two data validation lists.

In the first list, the user can choose either Fruit, Vegetable or Meat.

The second list is dependent on the first list. It will provide a user a choice of either Fruit, Vegetables or Meat depending on what was chosen in the first list. E.g. if the user picked Vegetable in the first list, the second list will give him/her the option of Lettuce, Tomato, Carrot or Avocado.

List filtered.jpg

Say the user picks Lettuce. I was there to be a cell elsewhere the displays the price of Lettuce. In this case, it would show 2.5 (value in cell D2).

Assumptions:
- the price of the object is always in a cell adjacent and to the right of the item in the data source table.

Issues:
- as you can see, due to the nature of the columns vlookup and match isn't really an option (unless there is a clever way to do this)
- you can't simply create code to look up the end result as you have items like Avocado which appear in both lists.
- items like Avocado may appear in multiple columns, but the price may differ in each column. This again rules out the ability to just search an string containing "Avocado" and returning the price.

This data is test data created for this example. I'm afraid I can't provide the real data due to work confidentiality. Happy to answer any questions.

Appreciate the help.

s.

Attached the a sample sheet which replicates the original.

Thanks

Wow this is great, thanks very much. I'll have to hide the formula columns but this shouldn't be a problem. Thank you!