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

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

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.

2. ## Re: Index-Match type lookup to assign phrase to a number from a MaxDiff type survey matrix

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)))

3. ## Re: Index-Match type lookup to assign phrase to a number from a MaxDiff type survey matrix

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?

4. ## Re: Index-Match type lookup to assign phrase to a number from a MaxDiff type survey matrix

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

5. ## Re: Index-Match type lookup to assign phrase to a number from a MaxDiff type survey matrix

=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.

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.

6. ## Re: Index-Match type lookup to assign phrase to a number from a MaxDiff type survey matrix

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)))

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1