FormulaCamp
Completed
5. Advanced Formulas · Modern Functions · 30 min

20. Practice: Modern Functions

Lesson 20 of 20
Task 1 of 5

  1. In D2:D11, find the city: extract the letters at the start of the code (REGEXEXTRACT) and look them up in the reference table I2:J4 (XLOOKUP).

Lesson theory

An export of orders: the order code starts with a city code, and the clients are both companies and private individuals. Build the report using only modern functions:

  • REGEXEXTRACT + XLOOKUP return the city from the order code;
  • REGEXTEST flags a company (the name ends in LLC or Inc);
  • GROUPBY returns revenue by city;
  • FILTER lists the companies' orders;
  • LET calculates the share of companies in revenue, with clear names inside the formula.

The company flag is calculated in a helper column: it is computed once, and FILTER and LET just refer to it.

Test yourself

1. Why is the company flag placed in a helper column E?
2. What does XLOOKUP return if the city code is not in the reference table and the fourth argument is omitted?
3. What does LET(tot,SUM(C2:C11),…) do?

Practice is part of Pro

The live spreadsheet, task checking and .xlsx files in all 170 lessons. Your progress is saved in this browser; sync across devices is coming soon.

Get ProFree lessons