Results 1 to 6 of 6

Thread: Find a value in a matrix using multiple criteria and greater than/less than

  1. #1

    Find a value in a matrix using multiple criteria and greater than/less than



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi all, hoping someone might be able to help me out with this formula. What I have is a table like the one below. I can amend the values in the table (particularly the last column) to make the formula work.

    A B C D E
    1 40% 35% 30%
    2 $250,000 0.5% 1.5% 2.5% <25%
    3 $250,000 1.0% 2.0% 3.0% >25%
    4 $500,000 1.0% 2.0% 3.0% <15%
    5 $500,000 1.5% 2.5% 3.5% >15%
    6 $1,000,000 1.0% 2.0% 3.0% <10%
    7 $1,000,000 2.0% 3.0% 4.0% >10%

    I'm trying match the first column, first row and last column to specific cells, however, none of the numbers that I'm trying to match are exact matches. For example, one set of data looks like this:

    C D E
    14 $1,962,939.84 16.8% 23.65%

    So I need to recognize that cell C14 is equal to or greater than 1,000,000, Cell D14 is less than 30%, and cell E14 is greater than 10%, therefore the formula should return the value shown in cell D7.

    Could someone help?

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,229
    Articles
    0
    Try:

    =INDEX(OFFSET(INDEX($B$2:$D$7,MATCH(C14,$A$2:$A$7),0),-1,,2,),MATCH(E14,OFFSET(INDEX($F$2:$F$7,MATCH(C14,$A$2:$A$7),0),-1,,2,)),MATCH(D14,$B$1:$D$1,-1))


  3. #3
    That works except when D14 is over 40% & when E14 is less than the corresponding values in E2:E7.

    D14 I can just handle easily with an IFERROR at the front of the statement, since if the value in D14 is over 40 the formula should always return a 0. So I modified the formula to look like this:
    =IFERROR(INDEX(OFFSET(INDEX($D$4:$F$9,MATCH(C13,$C$4:$C$9),0),-1,,2,),MATCH(E13,OFFSET(INDEX($G$4:$G$9,MATCH(C13,$C$4:$C$9),0),-1,,2,)),MATCH(D13,$D$3:$F$3,-1)),0)

    I don't understand the formula enough to make the appropriate changes to correct the column E issue though.

  4. #4
    Nevermind, simply changing the values in cells E2, E4 & E6 to 0% resolved the column E issue. Thank you for the solution!

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,229
    Articles
    0
    Shoot... I forgot to mention that I had added column F (F2:F7) to list column E as 0, 25, 0, 15, 0, 10 which is pretty much what you figured out by revising column E.... sorry about that... but glad you figured it out.


  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    671
    Articles
    0
    Quote Originally Posted by Joecam View Post
    Nevermind, simply changing the values in cells E2, E4 & E6 to 0% resolved the column E issue. Thank you for the solution!
    Also you could insert an extra column to cater for percentages above 40 in row one. Even if these aren't valid, where they are part of a table of formula parameters, its better to include them annd return 0, "", "Invalid" or whatever. This is because you have greater flexibility than with the single IFERROR() value, which must cover all errors.
    See my attachment.
    Attached Files Attached Files

Posting Permissions

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