ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Vlookup, Sort ?? (https://www.excelbanter.com/excel-discussion-misc-queries/68387-vlookup-sort.html)

M.A.Tyler

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!



Max

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!





M.A.Tyler

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!






Max

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?




Max

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
---



M.A.Tyler

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?





Max

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.





All times are GMT +1. The time now is 11:15 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com