Function that give me the nth large number of the two sheets, with criteria

[rnf]

Hi,

I would like your help to solve this problem. I have two sheets: A and B.

In sheet A I have:

A1 = JAN
A2 = JAN
A3 = FEV
A4 = FEV
B1 = 1
B2 = 2
B3 = 3
B4 = 4

In sheet B I have:

A1 = JAN
A2 = JAN
A3 = FEV
A4 = FEV
B1 = 10
B2 = 20
B3 = 30
B4 = 40

So, I would like to have a function that give me the nth large number of the two sheets, with criteria.

For example, I tried this formula but doesnt work: =LARGE((A:B!$A$1:$A$4="JAN")*(A:B!$B$1:$B$4);1)

Any thoughts?

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new, I have done it for you this time.)

[rnf]

Thank you Ali for the heads-up.
I edited the post with an attachment.
For a single sheet I can do it with this formula: =LARGE(IF(Sheet2!A2:A21=B2;Sheet2!B2:B21);1) or =LARGE(IF(Sheet1!A2:A21=B2;Sheet1!B2:B21);1)
I think that the problem is when I try to create a range from multiple sheets.

[JeteMc]

This proposal employs a helper column (D) on sheets 1 and 2.
The helper columns are populated using: =AGGREGATE(14;6;B$2:B$21/(A$2:A$21='TOP10'!B$2);ROWS(A$1:A1))
Note that 'TOP10'!B$2 is populated using data validation (dropdown).
Cells B2:B6 on the TOP10 sheet are populated using: =LARGE(sheet1:Sheet2!D2;1)
Let us know if you have any questions.

[rnf]

Thank you JeteMc for your proposal!
I think that this doesnt solve my problem because the formula =LARGE(sheet1:Sheet2!D2;1) give me the large number of n#TOPs from sheet1 and sheet2. For example, if in the sheet1 we have TOP#1=100 and TOP#2=98, and in the sheet2 we have TOP#1=99 and TOP#2=97, the formula =LARGE(sheet1:Sheet2!D2;1) will give TOP#1=100 and TOP#2=98. And should be TOP#1=100 e TOP#2=99.

[rnf]

After many hours looking for a solution, I think that I found a go-around.
I created a new sheet, DATA, that aggregates all sheets in one single table. With this table I can create a TOP table, that give me the values and the description associated, even if there are two or more equal values.
It is not what I was looking for but it works!
If someone knows how to avoid the step of aggregate the data, I will apreciate.

[JeteMc]

You are correct, sorry for making the mistake.
If you anticipate that the data may be updated later then you may want to consider converting the data on sheets 1:3 into tables.
Power Query could be utilized to produce a table (modeled in columns E:G on the Data sheet) using the following advanced editor code:

Please Login or Register  to view this content.

You might also consider using a pivot table to display descriptions of the top x values using more sort options and values filter.
When new values are added to one or more of the tables both the power query table and pivot table may be individually refreshed.
Let us know if you have any questions.

[rnf]

Thank you again JeteMc for your time to help me with my problem!
Its seem like we have 1001 ways to do the same think in excel!

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.