I have a document with two sheets.

One sheet has two fields:

Type of Fruit Color of Fruit

Example: Apple Red Apple Yellow Apple Blue Grape Red Grape Green

The other sheet is getting data from the former one and looks like this:

Apple Grape

And after apple i want a dropdown menu with all the colors.

So i need to get all colors in the first sheet for the fruit in the cell before the dropdown menu.

Tried using VLOOKUP but having issuses, can anyone guide me in the right direction?

Thanks all

CodePudding user response：

Solution:

You cannot create a dropdown list in Data Validation directly from a formula, but you can create a helper column to list your filter result and use that as the input range:

Helper Column:

=INDEX(FILTER(Sheet1!A1:B5,Sheet1!A1:A5=A1),0,2)

And then use that column as input: