Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching from a table
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching from a table
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))),VLOO KUP(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 wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching from a table
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&"_he ight")),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&"_hei ght")),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 On Jul 5, 11:46*am, wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching from a table
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 !!! Only tested out with your sample data - test it at the extremes, and come back if necessary. Hope this helps. Pete |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching from a table
You're welcome - thanks for feeding back.
Pete On Jul 6, 10:42*am, wrote: 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 !!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cross Reference Table Data Searching | Excel Worksheet Functions | |||
Searching Data Table | Excel Discussion (Misc queries) | |||
Table Searching | Excel Discussion (Misc queries) | |||
searching in more than one table | Excel Discussion (Misc queries) | |||
Searching for first and last in a table to chart (gantt) | Excel Discussion (Misc queries) |