How to use IF function if a number falls between a range of numbers

**cmhdeana** · 01-13-2012, 04:43 PM

I'm trying to figure out what function to use. I'm working on a form that needs to either display an amount depending on an age group, or highlight some cells depending on the age.

Example:

Cell A1 is 63
My requirements are as follows: Ages 62-65 would show $5,000, Ages 66-70 would show $4,500, Ages 71-75 would show $4,000

I figured I could use the IF function, but I can only figure out how to make it say $5,000 with only one number. How can I use a range of numbers to return the correct amount?

I tried nesting the function to say if it was 62, then say $5,000; if it was 63, say $5,000. But that didn't seem to work and from what I've heard, I can only nest up to 7 functions anyway.

Any help would be appreciated!

**JieJenn** · 01-13-2012, 05:27 PM

Something like this?

**teylyn** · 01-13-2012, 05:30 PM

Hello,

the easiest would be to create a lookup table that shows the age and the amount side by side. You only need to enter the lowest age for each amount, sort the table in ascending order of age. Then you can use a Vlookup formula with TRUE as the last parameter.