Creating a matrix to display data according to scoring

[susom]

I have a list of items and data that I need to map into a 3 by 3 cell matrix, and I am struggling to find a way to do this so that the items and data can be changed and matrix would be updated based on the new items/data.

Here is the sample of what I am looking to accomplish. I would want the matrix of Rating by Durability to be populated by the Units listed under the Item colum according to each item's Rating and Durability. How might I go about doing this?

Book1
	B	C	D	E
2		Strong	Moderate	Weak
3	Best	Unit 1	Unit 4
4	Better	Unit 5	Unit 2	Unit 6
5	Good		Unit 7 Unit 8	Unit 3 Unit 9
6
7		Item	Rating	Durability
8		Unit 1	Best	Strong
9		Unit 2	Better	Moderate
10		Unit 3	Good	Weak
11		Unit 4	Best	Moderate
12		Unit 5	Better	Strong
13		Unit 6	Better	Weak
14		Unit 7	Good	Moderate
15		Unit 8	Good	Moderate
16		Unit 9	Good	Weak
Sheet1

 

[awoohaw]

(edit: I just realized you seek multiple entries, i've deleted by suggestion.

What version of excel do you have. Please update your profile so it appears on your button and forum doesn't need to ask you every time.
The reason is that versions of excels may have features unavailable to other people.

 

[awoohaw]

This gets multiple entries, although there is an extra line after every duplicate record.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1		Strong	Moderate	Weak
2	Best	Unit 1	Unit 4
3	Better	Unit 5	Unit 2	Unit 6
4	Good		Unit 7 Unit 8	Unit 3 Unit 9
5
6		Item	Rating	Durability			Strong	Moderate	Weak
7		Unit 1	Best	Strong		Best	Unit 1	Unit 4
8		Unit 2	Better	Moderate		Better	Unit 5	Unit 2	Unit 6
9		Unit 3	Good	Weak		Good		Unit 7 Unit 8	Unit 3 Unit 9
10		Unit 4	Best	Moderate
11		Unit 5	Better	Strong
12		Unit 6	Better	Weak
13		Unit 7	Good	Moderate
14		Unit 8	Good	Moderate
15		Unit 9	Good	Weak
Sheet4

Cell Formulas
Range	Formula
G7:I9	G7	=CONCAT(IFERROR(INDEX($B$7:$B$15, IFERROR((1/($C$7:$C$15=$F7)/($D$7:$D$15=G$6)),"")*(ROW($B$7:$B$15)-ROW($B$7)+1),1)&CHAR(10),""))

 

[susom]

Thank you so much for this! I was thinking some combo of concatenate and index might do it. I will analyze this to make sure I understand how it's working. FWIW, I have a subscription to Office 365, and will try to figure out how to update that in my profile.

 

[awoohaw]

I have a subscription to Office 365

Okay, then the formula above can be updated to properly concatenate.,, I think.

 

[awoohaw]

Well, i get pretty much the same using TEXTSPLIT(TEXTJOIN())
I guess you could use either.

I could not get FILTER function to group the multiple items in one cell. Maybe someone will add that. But you have some working formulas, I think.

Book1
	F	G	H	I
6		Strong	Moderate	Weak
7	Best	Unit 1	Unit 4
8	Better	Unit 5	Unit 2	Unit 6
9	Good		Unit 7 Unit 8	Unit 3 Unit 9
Sheet4

Cell Formulas
Range	Formula
G7:I9	G7	=TEXTJOIN(,TRUE,(IFERROR(INDEX($B$7:$B$15, IFERROR((1/($C$7:$C$15=$F7)/($D$7:$D$15=G$6)),"")*(ROW($B$7:$B$15)-ROW($B$7)+1),1)&CHAR(10),"")))

 

[susom]

I have a follow on question to this solution. How might I go about using the CHAR(10) twice to create a larger blank space between the Units? I've been experimenting, but what I have tried has either resulted in the cell going blank, or an error in the formula...

 

[susom]

Never mind; I got it--an extra &CHAR(10) did the trick.

=TEXTJOIN(,TRUE,(IFERROR(INDEX($B$7:$B$15,
IFERROR((1/($C$7:$C$15=$F7)/($D$7:$D$15=G$6)),"")*(ROW($B$7:$B$15)-ROW($B$7)+1),1)&CHAR(10)&CHAR(10),"")))

 

[awoohaw]

Great, Happy to help.
Best Wishes!