Referencing data using the SMALL function

[HBuck]

Hello all,

I'm having a bit of a problem that I hope that someone can help me
with. Here's my sample data range:
City, Rate
Chicago, 50
New York, 23
San Francisco, 43
San Jose, 55
Baltimore, 14
Ann Arbor, 28

I want to populate a two-column, five row table with the city and rate.
I've used the SMALL function in the rate column to include the five
lowest rates. My problem is, I'm able to retrieve the rates, but I
can't populate the first column with the City.

What I need is if column B=14, then A=Baltimore and such. Does anyone
have any ideas how I can set that up?

Holli - who hopes that was semi-coherent

 

[Bob Phillips]

Use this to get the cities in order of smallest first

=INDEX($A$1:$A$100,MATCH(SMALL($B$1:$B$100,ROW(A1)),$B$1:$B$100,0))

and copy down

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Biff]

Try this:

This table is in the range A2:B7

Chicago, 50
New York, 23
San Francisco, 43
San Jose, 55
Baltimore, 14
Ann Arbor, 28

Enter this formula in D2:

=INDEX(A$2:A$7,MATCH(E2,B$2:B$7,0))

Enter this formula in E2:

=SMALL(B$2:B$7,ROWS($1:1))

Select both D2 and E2 and copy down 5 rows.

Based on your sample data there are no duplicate numbers. If there are this
will be more complicated. Post back if that's the case.

Biff

 

[HBuck]

Biff,

Going through the numbers, not only are there a few duplicates, but the
data is actually in more than one column. It just seems to get messier
as we go along. Can you please tell me how to work around that?

Thanks!
Holli

 

[HBuck]

Thanks for that, Bob!

Can you please tell me how I would do that for multiple columns? I
thought I had a handle on it, but I received an error message that I
can't seem to get myself out of.

Thanks,
Holli

 

[Bob Phillips]

What does the data look like?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[HBuck]

Thanks for responding, Bob.

The data looks like the following:

Cells B6 to C12

C-Sites Score
Detroit 25
Chicago 50
Baltimore 14
Atlanta 23
San Francisco 42
San Jose 39

Cells E6 to F9

D-Sites Score
Palo Alto 12
Canton 39
Omaha 14

I need to pull the city data from B7:B12, E7:E9 and the scores from
C7:C12, F7:F9.

There can definitely be duplicates in the scores.

Hope that this helps. Thanks!

Holli

 

[Pete_UK]

Holli,

does the data need to be in two separate tables? Can you combine it
into one table using another column for Site-type, like the following?

Type Site Score
C Detroit 25
C Chicago 50
C Baltimore 14
C Atlanta 23
C San Francisco 42
C San Jose 39
D Palo Alto 12
D Canton 39
D Omaha 14

This would make it a bit easier to do what you are asking.

Hope this helps.

Pete

 

[HBuck]

Pete,

I agree with you completely about it being much simpler to do what I
want. Unfortunately, I have no control over the way that the data is
actually listed in the worksheet. It was brought to me to see if I
could perform that specific function, leaving everything AS IS.

I wonder if what I am asking is even possible. I can call up the lowest
numeric values, but I can't match up the corresponding labels. If it
can't be done, then that is what I have to let them know. Either that,
or I can let them know that it would be better to list everything in
one table.

Thanks again!
Holli

 

[Biff]

I'm hesitant to say something can't be done but in this case......

I'll say that it can probably be done with VBA code but I don't know how to
do it using worksheet formulas. If you posted this in the programming forum
everyone that responds will tell you the same thing: put all the data in a
single table.

Biff

 

[JoyCarrot]

Hi Biff,

I accidentally found your postings in Google Groups from the following
link

http://groups.google.ca/group/micro...st+sorted+excel&rnum=4&hl=en#1564a6492673fe67

I think you are really good at excel.

I have the same problem as what the guy had. I tried what you wrote,
however, the formula MAX(IF(A1:A10<D1,A1:A10).... doesn't work for me.
It simply shows a #Value.

I was wondering if there is another way to find the maximum value that
is less than the lookup value in a unsorted range?

Your help is highly appreciated.

Thank you.

Karat

 

[Biff]

See this screencap:

http://img213.imageshack.us/img213/7764/maxifke1.jpg

Make sure you enter the formula as an array: CTRL,SHIFT,ENTER (not just
ENTER).

If there is no max value that is less than the lookup value then the formula
will eturn 0.

Biff