How to match values in a cell and return all matches (excel)

Question

I have a table in excel

Column 1 has a list of items.

I want to categorise them under different headings by matching them e.g. if the item was an ‘apple’ I would want to match that to the ‘fruit’ column. See example below. I know I have to create a list of all items under each of the categories (and then use a formula to match and return matching values) but struggling with finding the formula to match and return all matches.

Items	Clothing	Fruit	technology
Apple; pear; trousers; computer	trousers	apple; pear	computer

I have tried VLOOKUP but only returns one match

Answer 1

You could do this with a small powerquery

Load Table1 using data .. from table/range then file ... close and load ... connection only

Load Table2 using data ... from table/range then paste below into home...advanced editor ... and file... close and load back to excel as a table. Right click output table to refresh, or click refresh the query, or use VBA to refresh it

let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Index", {{"Items", Splitter.SplitTextByDelimiter("; ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Items"),
#"Merged Queries1" = Table.FuzzyNestedJoin(#"Split Column by Delimiter", {"Items"}, Table1, {"Column1"}, "Table1", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=false]),
#"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries1", "Table1", {"Column2"}, {"Column2"}),
#"Pivoted Column" =  Table.RemoveColumns(Table.Pivot(#"Expanded Table", List.Distinct(#"Expanded Table"[Column2]), "Column2", "Items", each Text.Combine(_, "; ")),{"Index"}),
Final = Table.FromColumns(Table.ToColumns(Source)&Table.ToColumns( #"Pivoted Column"), Table.ColumnNames( Source)&Table.ColumnNames( #"Pivoted Column"))
in Final