if index match match function

Question

I have a list of products in column A and list of their component materials in columns B, C, and D.

In order to visualize the data as required I need to rearrange the dataset to to where a unique list of the materials is in column A and the products in columns B-D.

I created the list of unique materials using

=INDEX(Range,1+INT((ROW(A1)-1)/COLUMNS(Range)),MOD(ROW(A1)-1+COLUMNS(Range),COLUMNS(Range))+1)

I need assistance in how to write the function to re-match the data from the original table (B10:D12).

Here's the link to the sample data set: https://docs.google.com/spreadsheets/d/1xz9YheNDtQhuR0s5CTieMZRVNMAZcGcPllC2gR4WgKk/edit#gid=0

Any help would be much appreciated!

Answer 1

use:

=ARRAYFORMULA(QUERY(IFERROR(SPLIT(FLATTEN(
 IF(B3:D6="",,B3:D6&"×"&A3:A6&"×"&B2:D2)), "×")), 
 "select Col1,max(Col3) 
  where Col1 is not null 
  group by Col1 
  pivot Col2
  label Col1'material'"))

---

or if you want to match it up:

=ARRAYFORMULA(IFNA(VLOOKUP(A10:A, QUERY(IFERROR(SPLIT(FLATTEN(
 IF(B3:D6="",,B3:D6&"×"&A3:A6&"×"&B2:D2)), "×")), 
 "select Col1,max(Col3) 
  where Col1 is not null 
  group by Col1 
  pivot Col2
  label Col1'material'"), {2, 3, 4}, 0)))

Answer 2

In B10, copied across and down :

=IFERROR(INDEX($B$2:$D$2,MATCH($A10,INDEX($B$3:$D$5,MATCH(B$9,$A$3:$A$5,0),0),0)),"")