age wise group data sort

[majidsiddique]

Hi all,

tell me how i can values in group wise below mention table.

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 64"]Person[/TD]
[TD="class: xl64, width: 64"]Age[/TD]
[/TR]
[TR]
[TD="class: xl63"]majid[/TD]
[TD="class: xl63"]19[/TD]
[/TR]
[TR]
[TD="class: xl63"]sajid[/TD]
[TD="class: xl63"]25[/TD]
[/TR]
[TR]
[TD="class: xl63"]khalid[/TD]
[TD="class: xl63"]20[/TD]
[/TR]
[TR]
[TD="class: xl63"]ahmed[/TD]
[TD="class: xl63"]60[/TD]
[/TR]
[TR]
[TD="class: xl63"]nasir[/TD]
[TD="class: xl63"]35[/TD]
[/TR]
[TR]
[TD="class: xl63"]khan[/TD]
[TD="class: xl63"]45[/TD]
[/TR]
[TR]
[TD="class: xl63"]gulraz[/TD]
[TD="class: xl63"]31[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 512"]
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]
Age-G[/TD]
[TD="class: xl65, width: 64"]majid[/TD]
[TD="class: xl65, width: 64"]sajid[/TD]
[TD="class: xl65, width: 64"]khalid[/TD]
[TD="class: xl65, width: 64"]ahmed[/TD]
[TD="class: xl65, width: 64"]nasir[/TD]
[TD="class: xl65, width: 64"]khan[/TD]
[TD="class: xl65, width: 64"]gulraz[/TD]
[/TR]
[TR]
[TD="class: xl66"]15-29[/TD]
[TD="class: xl65"]19[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]30-44[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]45-60[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
</tbody>[/TABLE]


for example Majid age is 45 appear in age group 45-60. i try this but failed. can anybody help me?

=IF(G$2=VLOOKUP(G$2,$A$2:$A$8,1,0),IF(AND($B2>=15,$B2<=29),$B2,"--"))
this formula never complete solution when trag it in column wise, fail.

trag it all 19 value paste in column wise.

thanks

 

[JazzSP8]

Assuming your Person / Age table is A1:B8 and the table you want to fill in is A10:H13 then this formula in B11 and dragged should work for you;

=IF(AND(VLOOKUP(B$10,$A$2:$B$8,2,FALSE)>=VALUE(LEFT($A11,2)),VLOOKUP(B$10,$A$2:$B$8,2,FALSE)<=VALUE(RIGHT($A11,2))),VLOOKUP(B$10,$A$2:$B$8,2,FALSE),"")

 

[Misca]

If your lookup table is in A2:B8 and the Age Group table in A13:H16 you can try something like:

=IF(AND(VLOOKUP(B$13,$A$2:$B$8,2,0)>=--LEFT($A14,2),VLOOKUP(B$13,$A$2:$B$8,2,0)<=--RIGHT($A14,2)),VLOOKUP(B$13,$A$2:$B$8,2,0),"")

This should work as long as both the min and max of each age group have 2 digits (= between 10 and 99). Otherwise you should replace the fixed length of two characters with a SEARCH of FIND function that locates the "-" from your age groups. Or if you want to get away real easy try having both max and min values of each age groups in helper columns ( = No need for double negatives or any other extra calculations).

 

[majidsiddique]

thanks both,
Misca and JazzSp8,
solved my problem
majid