Formula to determine whether value lies within range...

**Dpan131** · 12-13-2011, 05:56 PM

What do I do if I want to determine whether an input is within a specified range, and then for there to be an output of a certain value from another cell?

For example, with tax brackets, if income (D) falls within B and C, return value A(from a separate column)?

Here is the data I have:

Column A

"Formula referencing D1"
"Formula referencing D1"
"Formula referencing D1"
"Formula referencing D1"
"Formula referencing D1"

Column B

0
34500
83600
174400
379500

Column C

34500
83600
174400
379500
"infinity?"

Basically, what I want is a formula to put into column E that will do two things. First, to determine if the value in D1 is within either (B1-C1, B2-C2, B3-C3, B4-C4, or B5-C5). Second, to then output the value of either A1, A2, A3, A4, or A5, for each respective range.

Does that make any sense?! I tried to make it clear :/ Thanks so much guys!

**ATufts** · 12-13-2011, 06:48 PM

It would be a lot easier if you would put the example data into a worksheet which you attach to the post. One way that might work is to have a set of nested IF statements. Your conditions would start with the smallest or largest and do a comparison (less than or greater than). If it fails comparison, then output the percent for the highest or lowest bracket depending on where you started. If it passes, do another IF for the next threshold down. If you'll put some sample data into a worksheet I'll see if I can make it clearer.

Edit: Try this - File attached

**Dpan131** · 12-13-2011, 07:01 PM

Thank you for the example. The issue there is in E1. I would want the value in E1 to be 0.15, since 80000 is within 83600 and 177400. Thus I want the value returned to be from A3.

I did try the nested IF statements myself at one point, but there were too many and I got some sort of error.

Ugh, sorry, I just realized that I can't attach the sheet because I'm using "Numbers" on my iMac.

**vlady** · 12-13-2011, 07:35 PM

Hello
A lookup will do that..

Please Login or Register  to view this content.

BTW you don't need column c anymore...

Cheers.

**ATufts** · 12-14-2011, 03:08 PM

vlady got it better than I did. I was unfamiliar with the lookup function, learn something new everyday! If you wanted to modify the formula in the example that I gave, you would just need to remove the *D1 portions of the IF functions and it would return the same result.