age wise group data sort

[majidsiddique]

Hi all,

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

Person	Age
majid	19
sajid	25
khalid	20
ahmed	60
nasir	35
khan	45
gulraz	31

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

Age-G	majid	sajid	khalid	ahmed	nasir	khan	gulraz
15-29	19
30-44
45-60

<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>

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