Structured Tables, Names and dynamic list

[Badoumba]

Hi everyone,
New to Excel I try to achieve the following:

Table_c

Depending on the name selected ([Name]), I'd like to list possible actions ([Actions]) from table_b only for the type Grain (table_a), empty if it is Fruit.

I tried the following but can't solve the formulas errors:

1 - Retrieve corresponding type : works well with:

=INDEX(table_a[Type];MATCH(table_c[@Name];table_a[Name];0))

when typing this as a new column of table_c or side to it ith the rows corresponding, but I cannot convert it to a Named function (Name Manager) which would return is_of_type_grain as TRUE or FALSE. Seems like @ symbol does not work.
2 - I cannot find how to add a IF statement for a list based on the value of another cell from the table or using my named function directly which refers to this table cell.

All I find in Google is not directly related to these intricated issues.
Thanks for any help.

Badoumba

[#2]

@Badoumba Does the attached workbook do what you are trying to achieve? Data Validation does not accept structured table references, so you have to define a name for each column that you want to reference. For example, I defined names for lstNames and lstTypes to be used in the INDEX / MATCH lookup, as well as Grain and Fruit to be returned by the INDIRECT function as follows:

=INDIRECT(INDEX(lstTypes; MATCH($D3; lstNames; 0)))

I'm not exactly sure if this is what you were going for. Check it out and see...

[#3]

Hi @djclements
Yes, perfectly! A big thank you for the tip!.
Just for the sake of understanding, can $D3 be replaced by a name also? I tried with lstName_c (=table_c[Name]) but it is not working.

[#4]

@Badoumba Include the "@" symbol to indicate "This Row". Also, be sure to include the table name in the reference. For example, lstName_c: =table_c[@Name].

Having said that, the whole thing can be simplified by defining a name called lstActions with the following formula:

=INDIRECT(INDEX(table_a[Type]; MATCH(table_c[@Name]; table_a[Name]; 0)))

Then just use =lstActions as the Data Validation list source (see attached). Cheers!