Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup, Sort ??
Have a column of from 1 to 5 numbers, I would like to use only the first two.
Numbers are located in E16:E20. When I say first two, the two closest to the top of my column, which starts at E16. Hoping for help! Thanks in advance! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup, Sort ??
Essentially, believe you're asking the same question as your previous post
in mid Jan, re - earlier post / responses at: http://tinyurl.com/8tx5h But you didn't feedback further since .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "M.A.Tyler" <Great Lakes State wrote in message ... Have a column of from 1 to 5 numbers, I would like to use only the first two. Numbers are located in E16:E20. When I say first two, the two closest to the top of my column, which starts at E16. Hoping for help! Thanks in advance! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup, Sort ??
Hi Max, It is close to the same question, and I did try to use some
variations of that formula, without success. By the way that did work well for my earlier problem! This time I don't think I can use the row numbers, as they are 16-20. Also I would like to have the entry in the "E" column come back as the result. Something like this: "E" 16 102 17 18 100 19 99 20 So the result here would be 102 & 100 However it could look like this: "E" 16 100 17 89 18 99 19 100 20 98 Here the answer should be 100 & 89. Any ideas? "Max" wrote: Essentially, believe you're asking the same question as your previous post in mid Jan, re - earlier post / responses at: http://tinyurl.com/8tx5h But you didn't feedback further since .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "M.A.Tyler" <Great Lakes State wrote in message ... Have a column of from 1 to 5 numbers, I would like to use only the first two. Numbers are located in E16:E20. When I say first two, the two closest to the top of my column, which starts at E16. Hoping for help! Thanks in advance! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup, Sort ??
Adapting from the suggestion to your earlier post ..
here's one way, using non-array formulas .. Source range is in E16:E20 Put in F16: =IF(ROW(A1)2,"",IF(ISERROR(SMALL($G$16:$G$20,ROW( A1))),"",INDEX($E$16:$E$20 ,MATCH(SMALL($G$16:$G$20,ROW(A1)),$G$16:$G$20,0))) ) Put in G16: =IF(E16="","",ROW()) Select F16:G16, copy down to G20 F16:F17 will return the required results, i.e. the topmost 2 numbers other than blanks within E16:E20 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "M.A.Tyler" <Great Lakes State wrote in message ... Hi Max, It is close to the same question, and I did try to use some variations of that formula, without success. By the way that did work well for my earlier problem! This time I don't think I can use the row numbers, as they are 16-20. Also I would like to have the entry in the "E" column come back as the result. Something like this: "E" 16 102 17 18 100 19 99 20 So the result here would be 102 & 100 However it could look like this: "E" 16 100 17 89 18 99 19 100 20 98 Here the answer should be 100 & 89. Any ideas? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup, Sort ??
Alternatively, select F16:F17, put in the formula bar:
=IF(ISERROR(SMALL(IF(E16:E20<"",ROW(A1:A5)),ROW(A 1:A5))),"", INDEX(E16:E20,MATCH(SMALL(IF(E16:E20<"",ROW(A1:A5 )),ROW(A1:A5)),ROW(A1:A5), 0))) Then array-enter the formula (i.e. press CTRL+SHIFT+ENTER) instead of just pressing ENTER F16:F17 would also return the required results -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup, Sort ??
Max- Thank you again for your help! Both solutions worked well for my
application. "Max" wrote: Adapting from the suggestion to your earlier post .. here's one way, using non-array formulas .. Source range is in E16:E20 Put in F16: =IF(ROW(A1)2,"",IF(ISERROR(SMALL($G$16:$G$20,ROW( A1))),"",INDEX($E$16:$E$20 ,MATCH(SMALL($G$16:$G$20,ROW(A1)),$G$16:$G$20,0))) ) Put in G16: =IF(E16="","",ROW()) Select F16:G16, copy down to G20 F16:F17 will return the required results, i.e. the topmost 2 numbers other than blanks within E16:E20 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "M.A.Tyler" <Great Lakes State wrote in message ... Hi Max, It is close to the same question, and I did try to use some variations of that formula, without success. By the way that did work well for my earlier problem! This time I don't think I can use the row numbers, as they are 16-20. Also I would like to have the entry in the "E" column come back as the result. Something like this: "E" 16 102 17 18 100 19 99 20 So the result here would be 102 & 100 However it could look like this: "E" 16 100 17 89 18 99 19 100 20 98 Here the answer should be 100 & 89. Any ideas? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup, Sort ??
Glad to hear that !
Thanks for the feedback .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "M.A.Tyler" <Great Lakes State wrote in message ... Max- Thank you again for your help! Both solutions worked well for my application. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort with ISNA and vlookup | Excel Worksheet Functions | |||
Select rows and sort based on type | Excel Discussion (Misc queries) | |||
sorting more than 3 keys | Excel Discussion (Misc queries) | |||
Non-sequential VLOOKUP function -OR- sequential sort of web query | Excel Worksheet Functions | |||
"-" ignored in sort | Excel Discussion (Misc queries) |