Extracting Data Subject to Conditions

[andrewc]

Hi,

I have data in sheets "firm 1" and "firm 2" and would like to use formulae to extract the top and bottom 5 performance for each sector as shown in Sheet1.

The formulae in column B in Sheet1 would have to look in both adjacent sheets and return the largest and smallest 5 values for each sector and then formulae in column A would return the names associated with these values.

Can someone please suggest formulae to accomplish this.

Thanks!

[6StringJazzer]

This is very difficult to do with data distributed across two sheets. In fact I do not know how to do that.

In this solution I have combined the two sheets into one, adding a firm identifier. The formula to determine the top and bottom 5 uses an array formula. After typing in the formula, do not hit ENTER--hit CTRL+SHIFT+ENTER. You have done it correctly if the formula in the formula box has {braces} around it. You cannot type in the braces; they are just an indicator that it is an array formula.

{=LARGE(IF(Combined!D:D=Sheet1!C2,Combined!C:C,-9999),ROWS(A2:A2))}

Once we find the value, then we use another array formula to look up the combination of the value and the sector

{=INDEX(Combined!B:B,MATCH(B2&C2,Combined!C:C&Combined!D:D,0))}

[andrewc]

Thank you!

[sandy666]

another way with PowerQuery & PivotTable:

[6StringJazzer]

I forgot to attach the file.