Small, Vlookup, Not sure

[dcamos]

I am trying to create a formula to show the top,followed by 2nd, 3rd, ... and bottom, followed by 2nd, 3rd ... in a list.

Column A has unique values, Column C is what I want to return, Columns E and H have criteria and column S is what I want to find the largest and smallest of. I am not really sure how to go about it. Column S can have duplicates.

Thanks,

 

[njimack]

To retrieve the smallest value, use:
=INDEX($C$1:$C$10,MATCH(SMALL($S$1:$S$10,1),$S$1:$S$10,0))
change the "1" in the SMALL function to a 2 to find the 2nd smallest

 

[Steve=True]

you may have to create a dummy column of data where you add a really really really small number times the Row() number to your Column S and then use a small or large formula to get what you want.

Here is a good demonstration.

http://chandoo.org/wp/2009/11/12/topx-chart/

 

[barry houdini]

Perhaps try this:

In Y2 use this formula for the largest value in column S which matches the criteria in columns E and H

=LARGE(IF(E$2:E$100=X$1,IF(H$2:H$100=X$2,S$2:S$100)),ROWS(Y$2:Y2))

confirm with CTRL+SHIFT+ENTER and copy down as far as required to get a list of the column S values (in order) where the criteria match [assumes X1 and X2 contain the criteria]

Now in Z2 copied down for the matching column C values:

=INDEX(C$2:C$100,SMALL(IF(E$2:E$100=X$1,IF(H$2:H$100=X$2,IF(S$2:S$100=Y2,ROW(C$2:C$100)-ROW(C$2)+1))),COUNTIF(Y$2:Y2,Y2)))

also confirmed with CTRL+SHIFT+ENTER and copied down

adjust ranges as required, you can do the same with SMALL....

 

[Steve=True]

njimack, i like it. how does your fomula treat duplicates?

 

[barry houdini]

My formula will deal with duplicates. I'm assuming also that criteria need to be met in two other columns....

column Y will give a list of the numbers (from largest to smallest) where criteria are met. If that contains duplicates then the column Z formula will deal with that to pull out each column C value......

 

[njimack]

njimack, i like it. how does your fomula treat duplicates?

It doesn't - I missed that part of the OP. I'd probably use a helper column which took the value in col S + RAND()/1000