Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Need Help of Matrix (Excel 2003)

    Hi Excel Experts,

    I am back with my queries after a long time.

    I am attaching an excel in which i need the formula in Sheet "Master Data" to be fixed on Col J.

    There are 3 sheet in the file Matrix, Master Data, STI Matrix. In Master Data in Col H the formula check for ColA, F G and pick the value from Matrix Sheet and give me the out put. I need a similar formula in Col J. that will check the Col E & G and pick the value from STI Matrix and give me the output. I tried using the same formula from Col H by modifiying the same however i am not getting the desired out.

    Can you please help me fix the formula.

    Thanks
    Baiju
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Need Help of Matrix (Excel 2003)

    How about in J2:
    =INDEX('STI Matrix'!$B$3:$F$17,MATCH($E2,'STI Matrix'!$A$3:$A$17,0),MATCH($G2,'STI Matrix'!$B$2:$F$2,0))

    Copy down the column
    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Help of Matrix (Excel 2003)

    How about

    =INDEX('STI Matrix'!$A$3:$F$17,MATCH($E2,'STI Matrix'!$A$3:$A$17,0),MATCH($G2,'STI Matrix'!$A$2:$F$2,0))

    in cell J2, then fill down?

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help of Matrix (Excel 2003)

    How about:

    =INDEX('STI Matrix'!$B$3:$F$17,MATCH($E2,'STI Matrix'!$A$3:$A$17,0),MATCH($G2,'STI Matrix'!$B$2:$F$2,0))

    or:

    =INDEX('STI Matrix'!$A$3:$F$17,MATCH($E2,'STI Matrix'!$A$3:$A$17,0),MATCH($G2,'STI Matrix'!$A$2:$F$2,0))

    in cell J2, then fill down?

    <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Help of Matrix (Excel 2003)

    But did you create those formulas yourself...? Steve and I did. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  6. #6
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help of Matrix (Excel 2003)

    Yes. <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> using only only 3 keys <img src=/S/devil.gif border=0 alt=devil width=15 height=15>

  7. #7
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Need Help of Matrix (Excel 2003)

    Thanks Hans, Steve the formula is working perfect.. and thanks Nathan for consolidating the same :-)

  8. #8
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Need Help of Matrix (Excel 2003)

    HI Hans/Steve,

    I have made some changes to my STI Matrix to Include Min, Default & Max %. Earlier matrix had only the Default % and the formula works perfect.

    Is is possible with the new matrix have the Min STI% in Col J and Max % on Col L of the Master Data Sheet.

    I have attached the new file with the updated Matrix

    Thanks
    Baiju
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Help of Matrix (Excel 2003)

    I'd group the Min values together, the Default values together and the Max values together. That makes it easy to adjust the formulas.

    By the way, the grades in the new table don't match those in the old table - instead of 2 you now have 2.1 and 2.2 but column E in Master Data still contains 2, not 2.1 or 2.2.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Help of Matrix (Excel 2003)

    I have attached the workbook with the changes I'd make. I've used the old grades with the new percentages.
    Attached Files Attached Files

  11. #11
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Need Help of Matrix (Excel 2003)

    Thanks Hans,

    Its working perfectly.. You had not changed the ranges for Max STI which i have done.

    Thanks for the help

    Regards
    Baiju

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Help of Matrix (Excel 2003)

    Sorry, forgot about that at the last moment, but as you have found it's not difficult to modify the formula.

  13. #13
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Need Help of Matrix (Excel 2003)

    Hi Hans,

    I did figure out that 2.1 & 2.2 Missing. I have fixed that as well

    Thanks for the help

    Regards
    Baiju

Posting Permissions

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