Help with SMALL formula.

[Scott]

I have a range of 144 rows. A1:A144 contain names, B1:B144 contain values.
I've successfully used the SMALL formula to sort the data 1 through 144, but
I'd like to reference the corresponding names with the values. How do I do
that?

 

[Jacob Skaria]

Assuming you have the value generated from SMALL in C1 try the below

C1 =SMALL($B$1:$B$144,ROW())
D1 =INDEX($A$1:$A$144,MATCH(C1,$B$1:$B$144,0))

I assume you dont have any duplicate values

If this post helps click Yes

 

[Max]

You could also try this set-up. It includes tiebreakers to take care of the
possibilities of ties (or multiple ties) within the values in col B

Source data assumed in A1:B1 down,
names in col A, values (ie numbers) in col B
In D1: =IF(B1="","",B1+ROW()/10^10)
In E1:
=IF(ROWS($1:1)>COUNT($D:$D),"",INDEX(A:A,MATCH(SMALL($D:$D,ROWS($1:1)),$D:$D,0)))
Copy E1 to F1. Select D1:F1, copy down to cover the max expected extent of
source data. Minimize/hide col D. The automated results that you seek will be
returned in cols E and F, viz. an auto-ascending sort of the names and their
corresponding values, by values. Names with tied values (if any) will be
returned in the same relative order that they appear within the source.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik

 

[Scott]

There are duplicate values in this table. To determine an exact numeric
value in sorting I used the SMALL formula plus a ranking order of 1-144. So
this formula returns a unique value despite duplicate values. I just need to
match the name with the value.

Max, I tried your formula, but got a VALUE error.

 

[Max]

.. Max, I tried your formula, but got a VALUE error.

That means your data in col B is unfortunately mixed
with text/error values besides numbers

You could replace the earlier criteria formula in D1 with this:
=IF(ISNUMBER(B1),B1+ROW()/10^10,"")
Copy down, and it should work fine
Success? Pl click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik
---

 

[Scott]

Another problem. What happens when there is LESS than 144 rows of data? For
instance, there could be anywhere between 1 and 144 rows. With your formula,
I got it to work with 144 rows, but got an error with less than 144 rows of
data.

 

[Max]

That shouldn't be. The set-up I suggested was independent of the number of
rows of source data. Review it, re-check your adaptation/construct over
there. Ensure that the criteria col D and the extract formulas in cols E and
F are propagated correctly right down to cover the max expected extent of
source data.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik