age band formula require..

[VBABEGINER]

Based on below data, I need formula to get age band..
Values are in Col E. Output require in Col F.

Col E

17
13
11
14
12
17
18
19
18
16
14
18
10
15
13
15

Age band require..
(10-13,14-16,17-20)...

 

[oldbrewer]

[TABLE="width: 896"]
<colgroup><col width="64" span="14" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl24, width: 64, align: right"]17[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl24, align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl24, align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]5[/TD]
[TD]####[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl24, align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl24, align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl24, align: right"]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl24, align: right"]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl24, align: right"]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl24, align: right"]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl24, align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl24, align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl24, align: right"]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl24, align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl24, align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl24, align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl24, align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]###[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]=SUMPRODUCT(($A$1:$A$16>=G3)*($A$1:$A$16<=H3)*1)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcílio_Lobão]

VBABEGINNER, Good afternoon.

There are a lot of ways to solve this kind of question using Excel.

Using VLOOKUP function:

Scenario:

Your data --> E2:E17

Create a little table as Support: (suggestion only)

[TABLE="class: grid, width: 220"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]= K2 & "-" & L2[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]= K3 & "-" & L3[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]= K4 & "-" & L4[/TD]
[/TR]
</tbody>[/TABLE]

Try to use:

F2 -->

[B]=VLOOKUP(E2, $K$2:$M$4, 3, TRUE)[/B]

Copy the formulas down.

https://www.sendspace.com/file/qzg16h

Is that what you want?

I hope it helps.

 

[VBABEGINER]

Hi Oldbrewer, Thanks for reply. but i really dont understand solution. Thats fine.


Hello Marcílio,

Thanks for your reply. This works for me. Thanks for this trick.

VBABEGINNER, Good afternoon.

There are a lot of ways to solve this kind of question using Excel.

Using VLOOKUP function:

Scenario:

Your data --> E2:E17

Create a little table as Support: (suggestion only)

[TABLE="class: grid, width: 220"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]= K2 & "-" & L2[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]= K3 & "-" & L3[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]= K4 & "-" & L4[/TD]
[/TR]
</tbody>[/TABLE]

Try to use:

F2 -->

[B]=VLOOKUP(E2, $K$2:$M$4, 3, TRUE)[/B]

Copy the formulas down.

https://www.sendspace.com/file/qzg16h

Is that what you want?

I hope it helps.

 

[oldbrewer]

read up on the sumproduct function - it is so powerful

 

[Marcílio_Lobão]

VBABEGINER, Good evening.

Glad to have helped you.

Thanks for the feedback.

Have a nice week!