Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Double and Multiple Lookup Using the MATCH Function | Excel Worksheet Functions | |||
Match Index Lookup | Excel Discussion (Misc queries) | |||
Match then lookup | Excel Worksheet Functions | |||
Complex LookUp / Match Problem ?? | Excel Worksheet Functions | |||
Lookup then Match and insert value from next column | Excel Worksheet Functions |