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

    Not Picking the Right value from Matrix (Excel 2003)

    Hiii....

    Happy to see that the forum is up and running, been waiting for sometime now to post some of my queries that i had.

    I have a matrix and a data sheet, was able to build a formula based on some help i received, however its seem that the data sheet is not picking the right value from the Matrix.

    I have attached the excel sheet for your reference. Do let me know if there is a problem with the formula.

    I have a Matrix Range A2:E25, the matrix has the Group, Ranges and the recommended % Increase.

    I have the Data Sheet Range G2:J15, the formula is in Col J, which checks for the Group in Col G, the Compa in Col H and Rank in Col I and then pick the value from the Matrix based on these conditions.

    However the formula is not giving the right output.

    Can someone have a look at the formula tell me where im going wrong.

    Thanks in advance.

    Regards
    Baiju
    Attached Files Attached Files

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

    Re: Not Picking the Right value from Matrix (Excel 2003)

    Can you explain precisely and in detail what you want to accomplish? The values in column H do not correspond directly to those in column B.

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

    Re: Not Picking the Right value from Matrix (Excel 2003)

    Hi Hans,

    The values in Col B is 10.00%, 89.99%, 99.99%, 109.99%, 119.99% i have only change the cell format to display the Range.

    Want to Check the Group in Col G, Compa which range it falls in the Matrix and the Rank and give the % Inrease in Col J.

    Hope this clarifies.

    Regards
    Baiju

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

    Re: Not Picking the Right value from Matrix (Excel 2003)

    Hi Hans,

    Shall try and explain what exactly im looking.

    The Matrix A2:E25 is my reference table or matrix Which has Technology, Shared Services etc.,
    Col B is the Compa Ranges i.e. 10.00%, 89.99%, 99.99%, 109.99%, 119.99%, however i have formatted the column to display ranges, they are % values
    Col C:E is the % Increment Ranges

    Now in the Data Sheet i.e. G2:J15, the formula needs to check for the Group, Compa & Rank and pick the right % Increase from the Matrix in Col J ..
    for e.g. if the Group is Tech & the Compa is 100 i.e. > 99.99% < 109.99% thus picks up from Matrix 16.7%.

    However, the formula in the attachment is picking up from Operations - OE&ID. Can you figure out why?

    Regards
    Baiju

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

    Re: Not Picking the Right value from Matrix (Excel 2003)

    Try this in J3 and fill down:

    =INDEX($A$2:$E$25,MATCH(G3,$A$2:$A$25,0)+MATCH(H3, $B$3:$B$7)-1,MATCH(I3,$C$2:$E$2,0)+2)

    See attached version (I left the original formulas in column J and added the new ones in column K)
    Attached Files Attached Files

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

    Re: Not Picking the Right value from Matrix (Excel 2003)

    Hi Hans,

    This is exactly what i was looking for.. thanks a ton hans.

    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
  •