Vlookup, Sort ??

M

M.A.Tyler

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

M.A.Tyler

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

Max

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
 
M

Max

Alternatively, select F16:F17, put in the formula bar:

=IF(ISERROR(SMALL(IF(E16:E20<>"",ROW(A1:A5)),ROW(A1: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
 
M

M.A.Tyler

Max- Thank you again for your help! Both solutions worked well for my
application.
 
Top