Man, kenderweasel, you really drove me crazy!
I looked at your question, and my first thought was it's possible. Then as I deconstructed your formula, I decided it's definitely NOT possible! But it kept nagging at me all day, and I thought of some ways to get around the problem, only to have another one arise, then I'd figure that one out, and so on. So here's what I came up with:
| B | C | D | E |
|---|
| List | List | List | List | |
| xx | xx | cc | cc | |
| cc | cc | pp | pp | |
| pp | pp | xx | xx | |
| | | | |
<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
</tbody>
Sheet3
[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]{=IFERROR(
INDEX(Database!$B$1:$I$1,1,SMALL(IF(((Database!$C$1:$I$10=$A$16)+(Database!$C$1:$I$10=$A$17)+(Database!$C$1:$I$10=$A$18))*(Database!A$1:A$10=$H$7),COLUMN(Database!$C$1:$I$10)),ROW(A1))-(ROW(A$2)-1),1),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]{=IFERROR(
INDEX(Database!$C$1:$I$1,SMALL(IF(Database!A$1:A$10=$H$7,IF(ISNUMBER(MATCH(Database!$C$1:$I$10,$A$16:$A$18,0)),COLUMN(Database!$C$1:$I$10)-COLUMN(Database!$C$1)+1)),ROWS($C$2:$C2))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]{=IFERROR(
INDEX(Database!$B$1:$I$1,MATCH(0,MMULT(COLUMN(Database!$B$1:$I$1)^0,IF(IF(MMULT(TRANSPOSE(ROW(Database!$A$1:$A$10)),(Database!$A$1:$A$10=$H$7)*(COUNTIF($D$1:$D1,Database!$B$1:$I$1)=0)*((Database!$B$1:$I$10=$A$16)+(Database!$B$1:$I$10=$A$17)+(Database!$B$1:$I$10=$A$18))),Database!$B$1:$I$1,"a")>TRANSPOSE(IF(MMULT(TRANSPOSE(ROW(Database!$A$1:$A$10)),(Database!$A$1:$A$10=$H$7)*(COUNTIF($D$1:$D1,Database!$B$1:$I$1)=0)*((Database!$B$1:$I$10=$A$16)+(Database!$B$1:$I$10=$A$17)+(Database!$B$1:$I$10=$A$18))),Database!$B$1:$I$1,"a")),1,0))-SUM(IF(MMULT(TRANSPOSE(ROW(Database!$A$1:$A$10)),(Database!$A$1:$A$10=$H$7)*(COUNTIF($D$1:$D1,Database!$B$1:$I$1)=0)*((Database!$B$1:$I$10=$A$16)+(Database!$B$1:$I$10=$A$17)+(Database!$B$1:$I$10=$A$18)))=0,1)),0)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]{=IFERROR(
INDEX($B$2:$B$10,MATCH(0,IF($B$2:$B$10="",-1,COUNTIF($B$2:$B$10,"<"&$B$2:$B$10))-ROWS($E$2:$E2)+1-SUM(IF($B$2:$B$10="",1)),0)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
The first formula in B2 is your exact formula. The one in C2 does exactly the same thing, but a bit more concisely written. It helped to figure out exactly what yours was doing. Then the petite formula in D2 extracts the same values in sorted order. It's not extensively tested since I don't have your data to work with. Also note that I tested on a much smaller range than your original formula. If you want to try it, you'll have to change the ranges to match your sheet. HOWEVER, this formula is VERY computationally intensive, and I shudder to think about how it will slow down your sheet.
You'd probably be better off using your current formula in B2 as a helper column, then something like the formula in E2 to sort them.
So give me an interesting problem, and it'll nag at me until I solve it, but I spent a lot of time today working on a formula that will probably not be usable. If you give it a try, let me know how it works.
