#1   Report Post  
Posted to microsoft.public.excel.misc
M.A.Tyler
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
M.A.Tyler
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
M.A.Tyler
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sort with ISNA and vlookup Tom Excel Worksheet Functions 0 January 26th 06 09:01 PM
Select rows and sort based on type Sarah Excel Discussion (Misc queries) 0 October 11th 05 05:06 PM
sorting more than 3 keys Brooke Excel Discussion (Misc queries) 3 June 18th 05 04:52 AM
Non-sequential VLOOKUP function -OR- sequential sort of web query Eric S Excel Worksheet Functions 1 February 28th 05 08:50 PM
"-" ignored in sort Mike H Excel Discussion (Misc queries) 8 January 2nd 05 08:48 AM


All times are GMT +1. The time now is 05:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"