Compound Lookup, Large/Small, Match...

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.

For the part of finding the values, you can use array formulas like this:
=LARGE(--(CD1:CD5000>10)*X1:X5000,2)
Enter it with Ctrl+Shift+Enter. This will find the second large value on
column X with the condition on column CD.
The thing about locating it in the row where it happens can be done with a
formula like this in column E
=IF(AND(CD1>10,X1=Result),X1,"")
Where Result is the cell where you have the result of the previous formula.

Hope this helps,
Miguel.

"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.
>

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 <[email protected]>,
uw805 <[email protected]> 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.

Thanks for the suggestions. I'll try these tomorrow at work and post here if
I have any problems or questions. I appreaciate the help.

"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.
>