Index-Match type lookup to assign phrase to a number from a MaxDiff type survey matrix

[FivestarMac]

During a survey of 1000 respondents, when searching for the preferred 2 or 3 attributes from a list of 11 attributes, MaxDiff is preferred over simple ranking. In this MaxDiff example respondents answer 15 questions, each containing a mix of 4 Attributes of the 11 possible. Respondent checks his Most Preferred and Least Preferred from the 4 options. This exposes Respondent to at least 5 occurrences of each of the 11 attributes if 15 repetitions (questions) are employed. The matrix of combinations is obtained by Excel models on the internet for that purpose. However, the results come back as numbers and I need to convert the numbers to phrases in a list. I do not know how to do that, but believe it is best done with the use of Index/Match equations, or something equally elegant.
I would prefer a solution which would allow me to use more or fewer than the 15 sets or questions in my attached example.

[Glenn Kennedy]

I'm not sure that I follow what you want. This may or may not be correct!! If it is, I'll be happy to explain it...

=INDEX(Atributes!$B$2:$B$12,INDEX('Sets of 4'!$B$8:$P$11,1+MOD((ROWS($1:1)-1),4),MATCH(OFFSET($A$2,4*INT((ROWS($1:1)-1)/4),,,),'Sets of 4'!$B$7:$P$7,0)))

[FivestarMac]

Yes, it looks good for the 15 iterations. How would I expand to 20 iterations if I already had the info in "Sets of 4 Sheet" B8:T11?

[FivestarMac]

Sorry, B8:U11 would be the range for 5 more iterations.

[Glenn Kennedy]

=INDEX(Atributes!$B$2:$B$12,INDEX('Sets of 4'!$B$8:$U$11,1+MOD((ROWS($1:1)-1),4),MATCH(OFFSET($A$2,4*INT((ROWS($1:1)-1)/4),,,),'Sets of 4'!$B$7:$U$7,0)))

should do it... I'm aay for th enight. If it's not right, just shout. Otherwise...

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[LeoSkywalker]

AS a suggestion,

You could just transpose the data in SETS OF 4, so it can be better treated as a table.


Then you can add as many iterations as you need.

=INDEX(Atributes!$B$2:$B$12,INDEX('Sets of 4'!$B$8:$E$22,CEILING.MATH(ROWS($B$2:B2)/4),1+MOD(ROWS($B$2:B2)-1,4)))