+ Reply to Thread
Results 1 to 8 of 8

Formula to Return a pre-determined value - Between 2 other values

  1. #1
    Registered User
    Join Date
    03-02-2017
    Location
    South Africa
    MS-Off Ver
    2016
    Posts
    4

    Formula to Return a pre-determined value - Between 2 other values

    Hi All,

    can anyone please help me? I need a formula to return a pre-determanned tax value ( Col C ) where column A and B are the criteria

    EG: If someone earns R 6613, his Tax will be R 672. So the formula needs to take the earnings "X" and it needs to return the value of Col C where X >= col A and <= col B


    A B C

    R 6581 R 6596 R 667
    R 6597 R 6612 R 670
    R 6613 R 6628 R 672
    R 6629 R 6644 R 675


    Thanks in advance!

    Regards
    Last edited by fabio_rodrigues; 03-02-2017 at 06:57 AM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: Help!

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,671

    Re: Formula to Return a pre-determined value - Between 2 other values

    Are you familiar with Excel's lookup functions (starting with VLOOKUP())? https://support.office.com/en-us/art...8-93a18ad188a1 I sometimes wish there were more examples on the internet of using VLOOKUP() with the fourth argument as TRUE, but that is exactly what you want in this situation

    Assuming the values in the table are real numbers (and not text strings), something like =VLOOKUP(6613,$A$1:$C$5,3,TRUE) will return 672.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    03-02-2017
    Location
    South Africa
    MS-Off Ver
    2016
    Posts
    4

    Re: Formula to Return a pre-determined value - Between 2 other values

    Hi MrShorty,

    The Vlookup in the format you wrote wont work in this situation because the formula needs to search through the array, determining if X is greater or equal to column A, and smaller than or equal to column B, and if this is true,
    then to return the value in column C
    Last edited by fabio_rodrigues; 03-02-2017 at 12:44 PM.

  5. #5
    Forum Contributor
    Join Date
    05-06-2016
    Location
    Cluj, Romania
    MS-Off Ver
    2007-2010
    Posts
    220

    Re: Formula to Return a pre-determined value - Between 2 other values

    Hi,
    try this

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,671

    Re: Formula to Return a pre-determined value - Between 2 other values

    Do you have an example of it not working? I note that B1 (6596) is one less than A2 (6597), so the only lookup values that this would conceivably not work for are values between 6596 and 6597 (something like 6596.5). I have assumed that, because of the values listed in the table, that 6596.5 would be an invalid lookup value, and would be rounded up or down before performing the lookup.

    I ask for an example that does not work because that would help me understand what I am not understanding about the problem. If I understand the problem, a simple VLOOKUP() should work just fine. If it does not work, then I do not understand the problem.

  7. #7
    Registered User
    Join Date
    03-02-2017
    Location
    South Africa
    MS-Off Ver
    2016
    Posts
    4

    Re: Formula to Return a pre-determined value - Between 2 other values

    Hi Mr Shorty,

    I apologize, i missunderstood the formula you wrote to match exact values. It works perfectly. Thank you very much!!!!!

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: Formula to Return a pre-determined value - Between 2 other values

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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