Find top 2 lowest numbers and top 2 highest numbers

**photographex** · 02-12-2011, 10:46 AM

I want to find the 2 stores with the lowest number out and the 2 stores with the highest number out. I want to input the outs in one cell and the store number in the cell to the left. Sometimes we have 2 stores that have the same number. How can I get excel to put he store numbers in the cells to the left when they have the same number?

My formulas always return the same store for both cells.

**jeffreybrown** · 02-12-2011, 12:26 PM

How about in cell H30 and drag across

=INDEX($D$3:$J$3,MATCH(LARGE($D$5:$J$5,COLUMNS($A$1:A$1)),$D$5:$J$5,0))

Change the large to small for the other formula

**Ron Coderre** · 02-12-2011, 01:17 PM

Using your posted workbook....Try this:

This formula returns the Top Dept:

Please Login or Register  to view this content.

Since it appears that Col_F is a duplicate of Col_E

Please Login or Register  to view this content.

This formula returns the 2nd Place Top Dept:

Please Login or Register  to view this content.

...and...

Please Login or Register  to view this content.

Apply the same formulas to the Bottom Two Depts...
Largest value dept:

Please Login or Register  to view this content.

Second Largest Dept:

Please Login or Register  to view this content.

...and a correction to J43...it should be:

Please Login or Register  to view this content.

In your example...those formulas return:
First Place: 2789
Second Place: 3848
Last Place: 3807
Next-to-Last: 3801

Does that help?

**photographex** · 02-12-2011, 01:46 PM

Ok now we're starting to cook with some grease. Alright your formulas worked great but, where it says "store #1" and it list 2 stores, I want to use those 2 cells to give me the 2 different stores that had the same number.

To better explain: if 2789 had 2 and 3848 had 4 and 3864 had 4 also then I under store #1 would like excel to put nothing in cell E30 and 2789 in cell F30 and under store #2 I would like excel to put 3848 in cell H30 and 3864 in cell I30.

I hope this better explained my thought...You have been very helpful so far as well as "jeffreybrown"

**Ron Coderre** · 02-12-2011, 03:46 PM

Ok...I think we got it this time...
Still using your posted workbook..

New formula for the 2nd place value:

Please Login or Register  to view this content.

If there is a tie for First Place...
this is the first occurrence...Otherwise, a text-blank:

Please Login or Register  to view this content.

If there is a tie for First Place...
this is the second occurence...Otherwise, it is the First Place dept:

Please Login or Register  to view this content.

If there is a tie for second place...
this is the first occurrence...Otherwise, a text-blank:

Please Login or Register  to view this content.

If there is a tie for second place...
this is the second occurrence...Otherwise, the second place dept:

Please Login or Register  to view this content.

------------------------------------------
New formula for Next-to-Last Place:

Please Login or Register  to view this content.

If there is a tie for Last Place...
this is the first occurrence...Otherwise, a text-blank:

Please Login or Register  to view this content.

If there is a tie for Last Place...
this is the second occurence...Otherwise, it is the Last Place dept:

Please Login or Register  to view this content.

If there is a tie for Next-to-Last Place...
this is the first occurrence...Otherwise, a text-blank:

Please Login or Register  to view this content.

If there is a tie for Next-to-Last Place...
this is the second occurrence...Otherwise, the Next-to-Last Place dept:

Please Login or Register  to view this content.

How'd we do?

**photographex** · 02-12-2011, 05:12 PM

WOOOOOOHOOOOOO! You are the man....like we say at work that's a wrap. Now I need to copy everything over to all the cells and this should be good to go.

Now I will give careful study to the formulas so I may grow from young grasshoppa' to excel master such as yourself

Thank you, no how much do I owe....

**Ron Coderre** · 02-12-2011, 08:15 PM

A little gratitude is always nice....I'm afraid you've overpaid me!

**photographex** · 02-12-2011, 10:57 PM

I do appreciate it...besides I don't this will be the last time I reach out for help. I have to do a sheet for every month, Qtr, half to date, and year to date...fun!