For the second largest...

=INDEX(E2:E100,MATCH(LARGE(IF(CD2:CD100<"",IF(CD2 :CD100<=10,X2:X100)),2)
,IF(CD2:CD100<"",IF(CD2:CD100<=10,X2:X100)),0))

....confirmed with CONTROL+SHIFT+ENTER

For the second smallest...

=INDEX(E2:E100,MATCH(SMALL(IF(CD2:CD100<"",IF(CD2 :CD100<=10,X2:X100)),2)
,IF(CD2:CD100<"",IF(CD2:CD100<=10,X2:X100)),0))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
uw805 wrote:

I have a large spreadsheet, and I am looking for a formula to help me find
some values...

My spreadsheet has about 5,000 rows and 200 columns. I am looking for a
formula that will look up Large/Small values in column X, verify a condition
in column CD, and return the value in column E for that row. For example:

"Find the second largest value in column X, excluding any rows in which
column CD is greater than 10, and return the value in column E for that row."

One issue is that I can't sort the range at all, and I can't change the
order of the columns, so it has to be able to find these values in place.

Any suggestions? I thought something using Lookup, Sumproduct, or Match
might work, but I can't figure it out. Thanks.