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.

Compound Lookup, Large/Small, Match...
 

For the part of finding the values, you can use array formulas like this:
=LARGE(--(CD1:CD500010)*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(CD110,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.

Compound Lookup, Large/Small, Match...
 

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.

Compound Lookup, Large/Small, Match...
 

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.