List of type of materials

Occasional Contributor

I have been trying to list the types of materials in a list that relates the material (Table 1) with its type according to the final product (Table 2), so that I would obtain a list of type of materials without repetition and that is dynamic according to the product it's chosen (Table 3).
I made an example manually showing how it would look if I choose the product "hydrated ethanol".

Please, does anyone have any idea how to approach this situation?

Restrictions: it needs to work on Office 2016 without VBA nor Power Query.

5 Replies

@fhceq FILTER() may work for you.

=FILTER(ProductMaterial[Material Name],ProductMaterial[Product name]=K5)




Thank you, @Harun24HR, for your prompt response.
Unfortunately, there may have been some confusion.
I'm trying to list the material type according to the selected product. It is necessary to cross Table 1 with Table 2. Table 2 lists the materials of each product (sugarcane, hydrated ethanol and anhydrous ethanol). In table 1 the materials listed in Table 2 are grouped according to the type of material they are.
So, in Table 3, I would need to list the the type of materials ("water from hydro source", "reuse water"...) that are in the material types column of Table 1 without repeating, and this is dynamic according to the product (sugarcane, hydrated ethanol and anhydrous ethanol) that is chosen.
FILTER function does not work on Office 2016.


Excuse my saying so but I believe you have boxed yourself into a corner.  The obvious solutions are PowerQuery

    Source = Excel.CurrentWorkbook(){[Name="ProductMaterial"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product name", type text}, {"Material Name", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Product name] = SelectedProduct)),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"Material Name"}, MaterialType, {"Material name"}, "MaterialType", JoinKind.LeftOuter),
    #"Expanded MaterialType" = Table.ExpandTableColumn(#"Merged Queries", "MaterialType", {"Material Type"}, {"Material Type"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded MaterialType",{"Product name", "Material Name"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Material Type", Order.Ascending}})
    #"Sorted Rows"

or a 365 Dynamic Array solution

= LET(
    materialName, FILTER(ProductMaterial[Material Name], ProductMaterial[Product name]=product),
    materialType, XLOOKUP(materialName, MaterialType[Material name], MaterialType[Material Type],"Not found"),
    SORT( UNIQUE(materialType))

It may well be possible to put a solution together using only traditional spreadsheet techniques, but I suspect the cost of doing so would exceed the cost of obtaining several 365 licences.

Thank you so much, @Peter Bartholomew!
Unfortunately, it is not up to me the decision to buy the 365 licences, I wish I could.


Bad luck, it will be interesting to see whether you can deliver a solution for a lower cost than a license.

I think the primary need to find a workaround for the FILTER function.  Basically, you can use a sequence number based upon row numbers.  Then test and return each number only if the corresponding criterion is met.  The SMALL function will produce a consecutive list of indices that meet the criterion.  Use the list as indices within the INDEX function to return the filtered list.  Then use a further INDEX/MATCH to look up the material type.  I am just thankful that I do not need to do these things anymore!