+ Reply to Thread
Results 1 to 3 of 3

Recalling ranks from a matrix using vlookup & hlookup

  1. #1
    Registered User
    Join Date
    08-21-2011
    Location
    los angeles, ca
    MS-Off Ver
    Excel 2007
    Posts
    2

    Recalling ranks from a matrix using vlookup & hlookup

    Hi guys I’m new to excel and this forum and I am working on a spreadsheet that can recall the top 3 rankings of interactions between people in a matrix questionnaire.

    For example, I sent the questionnaire to each person and they are ranking who they interact with the most - 1 being the heights level of interaction and 3 being the lowest. (see image) The ranking is done up and down and the score are taken across. The lower the score across the matrix the higher interactions they have.

    \1

    Now, I want to recall each person’s #1, #2 & #3 from the matrix and automatically populate it into a list like the one below (see image). I would want to automatically fill in B9:D12 from the matrix above.

    \1

    I actually have a 28x28 matrix that needs to rank the top 5 to 7 but I’m using this as an example. I tried to use vlookup, hlookup, and rank functions but I wasn’t able to make it work
    Thanks in advance

  2. #2
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Recalling ranks from a matrix using vlookup & hlookup

    Hi,

    Not sure I fully understood your questions. Try this;

    B9, copy down & across.

    =INDEX($A$2:$A$5,MATCH(SUBSTITUTE(B$8,"#","")+0,INDEX($B$2:$E$5,0,MATCH($A9,$B$1:$E$1,0)),0))
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  3. #3
    Registered User
    Join Date
    08-21-2011
    Location
    los angeles, ca
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Recalling ranks from a matrix using vlookup & hlookup

    Thank you very much it worked!

    One more questions - say I were to ask them to rank games. Now A2 to A5 would change to baseball, basketball, soccer, football. B2 to E2 would stay the same with monica, chris, john, peter. How can I change the formuala to work with different things listed across from that ones listed up and down?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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