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

1. ## 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

Regards  Register To Reply

2. ## 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.

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)  Register To Reply

3. ## 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.  Register To Reply

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  Register To Reply

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

Hi,
try this

Formula:  `Please Login or Register  to view this content.`  Register To Reply

6. ## 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.  Register To Reply

7. ## 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!!!!!  Register To Reply

8. ## Re: Formula to Return a pre-determined value - Between 2 other values  Register To Reply