Advanced Formulas: practice exercises
These exercises are for people who already write everyday formulas and want to look up and reshape data. You will work with VLOOKUP, INDEX and MATCH, XLOOKUP, and the dynamic array functions FILTER, SORT and UNIQUE, then use LET and LAMBDA to keep long formulas readable. Tasks come with realistic lookup tables, so mistakes such as approximate matches or shifted ranges show up straight away in the automatic check.
69 exercises · level: Confident User
Start with the first task Study the course: Advanced Formulas →
Showing 69 of 69
Looking Up Data
In F2:F4, look up the item name for each code in column E.
In G2:G4, look up the item price.
In F2:F6, find each customer's discount from the scale on the left.
In G2:G6, calculate the amount to pay after the discount.
In G2:G4, find the employee ID for each name. The ID is to the left of the name, so VLOOKUP will not help.
In H2:H4, find each employee's salary.
In I2:I4, find the shipping cost for the city and weight in the table on the left.
In F2:F5, look up the item name for each SKU. If the SKU does not exist, show "Not in catalog".
In G2:G5, look up the price, and show 0 for an unknown SKU.
In D2:D5, pull the price from the "Prices" sheet.
In E2:E5, calculate the order total.
In D10:D13, look up the course name by code (exact-match VLOOKUP).
In E10:E13, find the monthly price with INDEX + MATCH.
In F10:F13, find the prepayment discount from the scale in F2:G5 (approximate match on the number of months).
In G10:G13, calculate the amount to pay: price × months × (1 − discount).
In J2:J4, find the course code by teacher (the code is to the left!). If there is no such teacher, show "No course".
Dynamic Arrays
In D2, list the cities without duplicates, in alphabetical order, with a single formula.
In E2, count the visits for each city in the list D2# with a single formula.
In G2, count how many different customers visited.
In E2, list all items in the category from K1 (all three columns).
Change the category in K1 to "Appliances". The list in E2 updates by itself.
In E7, list the items priced over $300, sorted by price from highest to lowest.
In A2, number the rows from 1 to 10 with a single formula.
In F4, split the answer in F2 into separate cities, one per cell.
In F7, rotate the table C1:D4 so the months run along a row.
In E2, list the three best rows by test 2 (all three columns).
In H2, list only the "Student" and "Test 1" columns for all rows.
In K2, build a single column: first the students in A2:A4, then A5:A7 below them, but without the first of those (use DROP).
In E2, list the courses without duplicates, in alphabetical order, with a single formula.
In F2, count the applications for each course in the list E2# with a single formula.
In G2, calculate the average score for each course in the list E2# with a single formula.
In I2, list all students sorted by score from highest to lowest with a single formula.
In H2, number the ranking places: as many numbers as there are rows in I2#.
In K3, list the students of the course in L1 whose score is 80 or higher.
Formula Builders
In A2:A10, number the rows with the ROW function.
In F1, calculate the revenue for the last 3 filled months with OFFSET.
In F5, pull the value from column C of the row whose number is in F4.
In B2:B5, show the login (the part before "@").
In C2:C5, show the domain (the part after "@").
In F2:F4, calculate the final price: a 10% discount, then 8% tax, rounded to whole dollars. Use LET.
In C2, calculate all prices with a 7% discount, rounded down to whole dollars, with a single formula.
In E2, build a running total of the prices with a single formula.
In G2, calculate the sum of the squared prices with REDUCE.
Give cell I1, which holds the rate, the name "PerLb".
In A2:A9, number the rows with the ROW function.
In C2:C9, show the shipment code: everything before " | ".
In D2:D9, show the destination: everything after " | ".
In F2:F9, calculate the cost: weight × PerLb, but not less than the minimum in I2. Use LET and the name "PerLb".
In I5 and I6, use OFFSET to pull the destination and the cost of the shipment whose number is in I4.
Modern Functions
In F1, use a single GROUPBY formula to calculate revenue by region, with headers and a total row. Use ranges that include the headers (A1:A13 and D1:D13).
In F8, list the average deal size for each manager: no headers (use B2:B13 and D2:D13), no total, from the highest average to the lowest.
In I2, list revenue by product for the region in J1 only (use the filter argument), without a total row.
In I8, count the deals for each product (the COUNT function), without a total row.
In F13, build a PIVOTBY summary: regions down the rows, products across the columns, sum of sales. Use ranges with headers.
In C2:C7, check whether the request contains the word "urgent" in any case ("urgent", "URGENT", "Urgent").
In D2:D7, extract the email address from the request. If there is no address, show "none".
In E2:E7, reduce the phone number to its 10 digits: remove everything except digits, then remove a leading country code 1 (US area codes never start with 1).
In F2, extract the year and the number from the order code "ORD-2026-0457" with one formula. They should spill into F2 and G2. Repeat for rows 3–7.
In H2:H7, format the phone number from E in the pattern "(415) 123-4567".
The formula in D2:D13 looks up the price twice. Rewrite it so the lookup runs once, and an unknown item (Salt) still gives 0.
The formulas in K2:K6 scan the entire columns B:B and D:D. Replace them with a single formula in K2 that uses exact ranges. It should spill over K2:K6.
H9 adds up the first N sales using the volatile OFFSET. Rewrite the formula without OFFSET and INDIRECT, so that it still depends on H8.
Make a parameter cell: enter the report date 10/31/2026 in H11.
TODAY() is in every row of E2:E13. Rewrite the formulas so they count the days from the report date in H11.
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).
In E2:E11, flag the companies: TRUE if the client name ends with "LLC" or "Inc".
In F1, use a single GROUPBY formula to calculate revenue by the cities in column D, with headers and a total.
In F8, list the client companies and the amounts of their orders (columns B and C) using the flag in E.
In J8, use LET to calculate the companies' share of total revenue: create the variables "tot" and "corp".
No exercises match these filters.