vlookup from a age range column and get the value from column 2

[jr7138]

Team:

I have a cell where I enter an age (1). When I enter my vlookup formula, it will not work due to the age column (A) being in a age range (40-45). Of course it works if I have a single age, say '40'. Does anyone have a simple solution? Please see my screen shot below:

1. Enter the age
2. Return Value =VLOOKUP(C14,F2:G11,2,TRUE) to return the value from Column "B"

A. Age Range column
B. Value to return

Thanks,
jer

 

[Fluff]

Hi & welcome to MrExcel.
If you change the values in col F to be like

+Fluff v2.xlsm
	F
1	Your Age
2	0
3	30
4	35
5	40
6	45
7	50
8	55
9	60
10	65
11	200
Main

then your formula should work.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. I have assumed that you enter the age in cell C14. Enter the age and press the RETURN key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C14")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim age As Range, vAge As Variant, i As Long
    If Target.Value <= 30 Then
        Target.Offset(, 1) = Range("G2").Value
    Else
        For Each age In Range("F3", Range("F" & Rows.Count).End(xlUp))
            vAge = Split(age, " - ")
            If Target.Value >= CLng(vAge(0)) And Target.Value <= CLng(vAge(1)) Then
                Target.Offset(, 1) = Range("G" & age.Row).Value
            End If
        Next age
    End If
    Application.ScreenUpdating = True
End Sub

 

[jr7138]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. I have assumed that you enter the age in cell C14. Enter the age and press the RETURN key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C14")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim age As Range, vAge As Variant, i As Long
    If Target.Value <= 30 Then
        Target.Offset(, 1) = Range("G2").Value
    Else
        For Each age In Range("F3", Range("F" & Rows.Count).End(xlUp))
            vAge = Split(age, " - ")
            If Target.Value >= CLng(vAge(0)) And Target.Value <= CLng(vAge(1)) Then
                Target.Offset(, 1) = Range("G" & age.Row).Value
            End If
        Next age
    End If
    Application.ScreenUpdating = True
End Sub

mumps:
That seemed to work, but only on the '30', all the others came up with this:

 

[jr7138]

Hi & welcome to MrExcel.
If you change the values in col F to be like

+Fluff v2.xlsm
	F
1	Your Age
2	0
3	30
4	35
5	40
6	45
7	50
8	55
9	60
10	65
11	200
Main

then your formula should work.

Fluff:
Not an option as it will be used by HR as a job-aid for employees. Must keep the age range as is. But, yes, that worked.

 

[Fluff]

In that case I think you will struggle to do it with a formula.

 

[jr7138]

In that case I think you will struggle to do it with a formula.

Mumps code worked, except for that little bug issue, but the value are populated.

 

[Fluff]

I'll leave that for mumps to handle, as it's his code.

 

[RossTattersall]

If you don't want to use VBA this works;

=SUMPRODUCT(--(SUBSTITUTE(RIGHT(F2:F11,2),"ve",10000)*1>=C14),--(SUBSTITUTE(LEFT(F2:F11,2),"Un",0)*1<=C14),G2:G11)

Here C14 is your input, F2:F11 is the column of your table with the ranges and G2:G11 is the column with the values to pick up. This will fail with age between 2 edge dates (e.g 34.5) but strictly speaking your table doesn't cover those. Can easily tweak it to look at the floor or ceiling of C14 to handle these if you like.

Let me know if you need me to explain the formula.

 

[jr7138]

If you don't want to use VBA this works;

=SUMPRODUCT(--(SUBSTITUTE(RIGHT(F2:F11,2),"ve",10000)*1>=C14),--(SUBSTITUTE(LEFT(F2:F11,2),"Un",0)*1<=C14),G2:G11)

Here C14 is your input, F2:F11 is the column of your table with the ranges and G2:G11 is the column with the values to pick up. This will fail with age between 2 edge dates (e.g 34.5) but strictly speaking your table doesn't cover those. Can easily tweak it to look at the floor or ceiling of C14 to handle these if you like.

Let me know if you need me to explain the formula.

RossTattersall:

Excellent formula. Worked like a charm, had to change the under 30 to under 29, because if I enter 30 it would add those two amounts from column 2.

Can you please explain the formula, like I'm a five-year old.

Thanks for you help.

Regards,
jer