Summary
This chapter covers how to perform multi-criteria sorting using SORTBY and how to extract the k-th largest value from a dataset with LARGE.

General domain of usage
Data reporting and analysis

`SORT` works well for basic ranking, but more advanced reports often require multiple sorting layers or independent sorting references. In this chapter, use `SORTBY` for multi-level ranking and `LARGE` for KPI extraction.

## SORTBY Structure

```excel id="1"
=SORTBY(array, by_array1, sort_order1, [by_array2, sort_order2])
```

* `array`: dataset being returned;
* `by_array1`: first sorting reference;
* `sort_order1`: first sorting direction;
* `by_array2`: optional second sorting reference;
* `sort_order2`: second sorting direction.

Unlike `SORT`, `SORTBY` uses external arrays instead of internal column positions.

## LARGE Structure

```excel id="2"
=LARGE(array, k)
```

* `array`: numeric values being evaluated;
* `k`: ranking position;
* `1`: largest value;
* `2`: second-largest value.

`LARGE` returns a single ranked value instead of a full sorted dataset.





Inside `Dynamic_Reports`, confirm the following inputs exist:

| Cell | Value |
| ---- | ----- |
| J2   | North |
| J4   | 3000  |



Step 1 Confirm Input Values


Replace the previous `SORT` formula with:

```excel id="3"
=SORTBY(
FILTER(Sales_Data!A2:H52,(Sales_Data!E2:E52=J2)*(Sales_Data!H2:H52>J4),"No results found"),
FILTER(Sales_Data!E2:E52,(Sales_Data!E2:E52=J2)*(Sales_Data!H2:H52>J4),"No results found"),1,
FILTER(Sales_Data!H2:H52,(Sales_Data!E2:E52=J2)*(Sales_Data!H2:H52>J4),"No results found"),-1
)
```

* First sorting layer: Region ascending;
* Second sorting layer: Revenue descending.



Step 2 Build the SORTBY Report



Review the output and confirm that rows are grouped alphabetically by region.



Step 3 Test Primary Sorting


Within each group, verify that Revenue values appear from highest to lowest.



Step 4 Test Secondary Ranking


All `FILTER` conditions inside `SORTBY` must return arrays with identical row counts.
If one array becomes misaligned, the formula fails.



Step 5 Validate Array Consistency


Inside a separate cell, type:

```excel id="4"
=LARGE(Sales_Data!H2:H52, 1)
```

This returns the highest Revenue value in the dataset.



Step 6 Extract the Highest Revenue Value


Replace the formula with:

```excel id="5"
=LARGE(FILTER(Sales_Data!H2:H52, Sales_Data!E2:E52=J2), 1)
```

* `FILTER(...)`: restricts the dataset to the selected region;
* `1`: returns the highest Revenue value.



Step 7 Add Dynamic Filtering to LARGE


Change the region value inside `J2`.
Notice that:

* The `SORTBY` report updates automatically;
* The `LARGE` KPI updates automatically.




Step 8 Test Dynamic KPI Updates

Why does `SORTBY` allow more flexibility than `SORT`?

What is a strict requirement for arrays used inside `SORTBY`?

What is the main difference between `SORT`/`SORTBY` and `LARGE`?

Build a complete Excel business analysis system using modern formulas, dynamic reporting, financial modeling, and scenario-based analysis. Throughout the course, you will transform raw transactional data into interactive dashboards, KPI engines, and scalable analytical models that reflect real-world business workflows.

Build the core Excel skills used in real business reporting workflows. Work with formulas, lookups, logical functions, conditional aggregation, error handling, and data cleaning while developing a structured sales analysis model.


Build dynamic Excel reporting systems using modern array functions and formula-driven workflows. Work with FILTER, SORT, SORTBY, UNIQUE, TRANSPOSE, spill ranges, interactive inputs, ranking systems, validation lists, and chart-ready data structures that update automatically as the dataset grows.


Build scalable relational Excel models using advanced lookup patterns and parameter-driven logic. Work with XLOOKUP, INDEX, MATCH, XMATCH, approximate matching, dynamic table relationships, scenario-based calculations, category-level aggregation, and flexible lookup architectures used in real analytical workflows.


Build advanced analytical and financial models that respond dynamically to changing data and assumptions. Work with time-based calculations, dynamic ranges, Named Ranges, installment modeling, investment analysis, amortization schedules, weighted aggregations, and scenario-driven financial metrics used in real business reporting systems.

Transform the workbook into a fully interactive business simulation system driven by scenarios, sensitivity analysis, and dynamic KPI logic. Build scenario engines, parameter-driven dashboards, sensitivity grids, advanced text-processing formulas, and centralized reporting layers that respond instantly to user inputs and business assumptions.

Advanced Sorting with SORTBY and LARGE

SORTBY Structure

LARGE Structure

1. Why does `SORTBY` allow more flexibility than `SORT`?

2. What is a strict requirement for arrays used inside `SORTBY`?

3. What is the main difference between `SORT`/`SORTBY` and `LARGE`?

Advanced Sorting with SORTBY and LARGE

SORTBY Structure

LARGE Structure

1. Why does SORTBY allow more flexibility than SORT?

2. What is a strict requirement for arrays used inside SORTBY?

3. What is the main difference between SORT/SORTBY and LARGE?

1. Why does `SORTBY` allow more flexibility than `SORT`?

2. What is a strict requirement for arrays used inside `SORTBY`?

3. What is the main difference between `SORT`/`SORTBY` and `LARGE`?