Searching from a table

[gojakie]

I have posted a question on mrexcel forum seeking help. I have got any
help yet and would request few experts out here to help me. I have
also posted there that I am seeking help from you guys too.

http://www.mrexcel.com/forum/showthread.php?t=328448

 

[sajay]

Name sheet1's male range MALE_TABLE
170 54-67 56-70 56.5-71.5 58-74.5 59-75
171 55-68 57-71 57.5-72.5 59-75.5 60-76
172 55.5-68.5 57.5-72 58-73.5 59.5-76.5 60.5-77
173 56.5-69.5 58.5-73 59-74.5 60.5-77.5 61-78
174 57-70 59-73.5 59.5-75 61-78 61.5-79

Name sheet1's female range FEMALE_TABLE

157 44.5-54.5 46-56 47-57.547.5-58 48.5-59 49-59.5
158 45-55 46.5-56.5 48-58.5 49-59.5 49.5-60
159 45.5-55.5 47-57.5 48.5-59.5 49.5-60 50-60.5
160 46-56 47.5-58 49-60 50-60.5 50.5-61.5
161 46.5-56.5 48-58.5 50-60.5 50.5-61 51-62


Put a formula in a column adjascent to Sheet 2 difference column name it as
AGE RANGE (in my example it is "Q" . like this
here

=IF( AND (J3>=18,J3<=22), 1, IF( AND (J3>=23,J3<=27), 2, IF ( AND
(J3>=28,J3<=32) ,3 ,IF (AND (J3>=33,J3<=37),4, IF( AND(
J3>=38,J3<=42),5,0)))))

terminology
J :- stands for age in Sheet 2
1,2,3, & 4 :- are meant for column number male / female list



create another column next to this new column as above

=IF(NOT(ISERROR(VLOOKUP(K3,MALE_TABLE,Q3,0))),VLOOKUP(K3,MALE_TABLE,Q3,0),VLOOKUP(K3,FEMAIL_TABLE,Q3,0))


here k3 denotes hight in cms in sheet2
Q3 is the column first created!

the results will be like this?



AGERANGE M4
59-74.53
56-703
47-57.5if you want to go further like calculating differnces from this range,
better convert sheet1 to suit this needs

Yours,
sajay

 

[Pete_UK]

First, set up some named ranges:

M (for Male) covers Sheet1!$C$5:$G$9
F (for Female) covers Sheet1!$J$5:$N$9
M_height covers Sheet1!$B$5:$B$9
F_height covers Sheet1!$I$5:$I$9
Ages covers Sheet1!$B$12:$B$17

Put these age range start values in B12:B17

18
23
28
33
38
42

Then in M3 of Sheet2 you can use this formula:

=LEFT(INDEX(INDIRECT(B3),MATCH(K3,INDIRECT(B3&"_height")),MATCH(J3,Ages)),SEARCH("-",INDEX(INDIRECT(B3),MATCH(K3,INDIRECT(B3&"_height")),MATCH(J3,Ages)))-1)

and this one in N3:

=MID(INDEX(INDIRECT(B3),MATCH(K3,INDIRECT(B3&"_height")),MATCH(J3,Ages)),SEARCH("-",INDEX(INDIRECT(B3),MATCH(K3,INDIRECT(B3&"_height")),MATCH(J3,Ages)))
+1,255)

which will give you what you want. You can then copy these two
formulae down to row 5.

Note that L5 in Sheet1 needs to be editted.

Only tested out with your sample data - test it at the extremes, and
come back if necessary.

Hope this helps.

Pete

 

[gojakie]

Thank you Pete and Sajay for looking into my problem. Pete's solution
also takes care of calculating difference without converting sheet1.
Thank you very much once again to both of you.... you rock !!!

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete