Insert value based on range

G

GL

Here is what I'm trying to accomplish...I want a formula that will populate
the rate based on the employee's age...
So I want it to compare column C to the range in column A and return the
rate from column B to Column D.

As far as I can tell, there are too many options to use nested IF
statements. Can anyone help?

A B C D
Age Employee EE Age Rate
0-24 $0.05 33 0.08
25-29 $0.06
30-34 $0.08
35-39 $0.09
40-44 $0.10
45-49 $0.15
50-54 $0.23
55-59 $0.44
60-64 $0.68
65-69 $1.27
70+ $2.06
 
P

Pete_UK

I would set your table up slightly differently so that you have only
the start age in column A, like this:

A B C D
Age Employee EE Age Rate
0 $0.05 33 0.08
25 $0.06
30 $0.08
35 $0.09
40 $0.10
45 $0.15
50 $0.23
55 $0.44
60 $0.68
65 $1.27
70 $2.06

Then in D2 you can have this formula:

=VLOOKUP(C2,A$2:B$12,2)

Hope this helps.

Pete
 
S

Sean Timmons

you'll want VLOOKUP, but get rid of the -24 stuff.

so:

0
25
30
35
40
etc

for ages

Then =VLOOKUP(C2,A:B,2) will return your rate.

Cleanest way to get rid of the -'s is

Highlight column A

Find -*

Replace With and leave the box blank

Replace all

Then just manually get rid of the + at the end
 
T

T. Valko

Split your age range into 2 cells:

0-24

Should be:

0.....24

Where 0 is in one cell and 24 is another cell.

With your last boundary ,70+, enter it simply as 70.

Then you do a lookup on the *lower boundary* age range of cells.

Assume:

A1:11 = lower age boundary
B1:B11 = upper age boundary
C1:C11 = numeric values

D1 = 33

=VLOOKUP(D1,A1:C11,3)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pricing Chart 3
I need Time Range to return Value 4
If Then Else?? 0
vlookup error 3
Logarithmic Trendline 2
Define a Range based on Cell Values 2
lookup based on date range 4
Determine Value based on criteria 1

Top