+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Forum Contributor
    Join Date
    01-19-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    108

    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.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,820

    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)))
    Attached Files Attached Files
    Glenn



  3. #3
    Forum Contributor
    Join Date
    01-19-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    108

    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. #4
    Forum Contributor
    Join Date
    01-19-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    108

    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. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,820

    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.



    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.

  6. #6
    Forum Contributor
    Join Date
    10-08-2018
    Location
    Lima, Peru
    MS-Off Ver
    Office 365 ProPlus
    Posts
    115

    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)))
    Attached Images Attached Images
    Attached Files Attached Files
    Leo Skywalker
    May the force be with you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Index Match-Sumproduct- Lookup-type formula?
    By eyoonbbj in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-23-2014, 11:45 AM
  2. [SOLVED] Index Match-Sumproduct- Lookup-type formula?
    By eyoonbbj in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-23-2014, 11:15 AM
  3. [SOLVED] Index/Match lookup with two columns - Runtime Type 13 Error
    By connor12 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-17-2014, 09:52 PM
  4. [SOLVED] Only text values matching using index/match lookup - data type pro
    By Dave Peterson in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 07:05 AM
  5. [SOLVED] Only text values matching using index/match lookup - data type pro
    By Dave Peterson in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 06:05 AM
  6. [SOLVED] Only text values matching using index/match lookup - data type pro
    By Stanton in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  7. Only text values matching using index/match lookup - data type pro
    By Stanton in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM

Bookmarks

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