A high score from the wrong team appears
Both row conditions must hold. A qualifying score alone does not select a record.
Build a filtered priority queue with a stable tie rule
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter one formula in G5 to return matching A2:D9 records for team G2 and points at least G3. Sort by points descending, then numeric ticket ascending. With no matches, return No matches in G5 and leave the rest of G5:J12 blank.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G5:J12. Enter one formula in G5; keep the rest of the output area clear. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Select one team at or above a points threshold, then sort the complete records by points descending and numeric ticket ascending.
THE QUICK ANSWER01
Select one team at or above a points threshold, then sort the complete records by points descending and numeric ticket ascending.
ƒx
=LET(keep,(B2:B9=$G$2)*(D2:D9>=$G$3),IF(SUM(keep)=0,"No matches",LET(records,FILTER(A2:D9,keep),SORTBY(records,CHOOSECOLS(records,4),-1,CHOOSECOLS(records,1),1))))| Ticket | Team | Owner | Points |
|---|---|---|---|
| 101 | Ops | Ari | 8 |
| 103 | Ops | Chen | 8 |
| 106 | Ops | Fran | 7 |
| 108 | Ops | Hana | 6 |
The remaining reserved output cells are blank. Keep that area clear when trying the formula.
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
The original queue
Tickets 101 and 103 both have eight points, so numeric ticket order puts 101 first. They are followed by 106 with seven and 108 with six. Product tickets are outside the chosen team even when they have more points.
A tie needs its own key
Sorting only by points leaves the order of equal scores outside the stated rule. The second SORTBY key makes the result reproducible when source rows are rearranged. Ticket IDs are distinct whole numbers in this task.
Matching and input rules
Team equality ignores letter case and treats spaces and punctuation literally. A team named A* is a literal label. All eight source rows have numeric IDs and points plus nonempty ASCII team/owner text; the numeric controls accept values between -1,000,000 and 1,000,000.
The output area is a capacity limit
This exercise has eight source rows and room for all eight four-column results. The checker compares every output position, including blanks left after a shorter report. Entering formulas into spill children is not a reusable single-anchor solution.
Read the full step-by-step walkthrough
- Define the two row conditions
A record must belong to the selected team and meet the inclusive minimum. Multiplication combines the two Boolean conditions for each row.
- Name and reuse the selection
LET names the keep mask so both the empty check and FILTER use exactly the same population.
- Keep all fields together
FILTER returns the whole ticket, team, owner and points row. Filtering a single column would lose the relationship between fields.
- Sort by points, then by ticket
CHOOSECOLS reads each key from the filtered records. Points sort descending; a numeric ticket ID breaks tied scores in ascending order.
- Give an empty queue a defined result
When the mask contains no selected row, return one text cell. The rest of the reserved output area must become blank.
- Let one formula own the report
Enter only G5 and keep G5:J12 clear. Changing the team or minimum can grow or shrink the spill; a typed value inside it blocks calculation.
Things to watch
Equal scores change order
Use ascending numeric ticket ID as the second SORTBY key.
The first cell returns #SPILL!
Clear typed values in G5:J12 while retaining the anchor formula. Scroll within the worksheet to reach any blocked output cell.
About this example
The single anchor formula must spill into a clear output area. Local browser calculation and independently defined task rules are checked separately from native Excel/Google Sheets compatibility. Use a desktop Excel version supporting all named functions to recalculate a download; native editing/save/reopen is unverified.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.