Excel: Lookup and Logical Functions
Excel: Lookup and Logical Functions is an intermediate-level course of 5 lessons (about 1 h 35 min), with 16 exercises and 15 quiz questions. Prerequisites: Excel Fundamentals. The course needs a Windows virtual machine with Power BI, planned for a later phase of the platform.
- Excel
- Intermediate
- 5 lessons
- 1 h 35 min
- 16 exercises
- Windows virtual machine (phase 2)
The course in brief
This course teaches you the functions that save you hours every day: matching two tables with RECHERCHEX (XLOOKUP) or INDEX/EQUIV (INDEX/MATCH), translating business rules with SI.CONDITIONS (IFS), ET (AND) and OU (OR), building summaries with SOMME.SI.ENS (SUMIFS) and NB.SI.ENS (COUNTIFS), cleaning up text and calculating due dates. Finally, you learn to handle errors without hiding real anomalies. Every lesson relies on a small dataset and exact results to check.
What you will be able to do
- Match two tables on a key with RECHERCHEX (XLOOKUP), RECHERCHEV (VLOOKUP) or INDEX/EQUIV (INDEX/MATCH)
- Translate a multi-tier, multi-criteria business rule with SI.CONDITIONS (IFS), ET (AND) and OU (OR)
- Build summaries with SOMME.SI.ENS (SUMIFS), NB.SI.ENS (COUNTIFS) and MOYENNE.SI.ENS (AVERAGEIFS), including over periods
- Clean up and split text, and calculate due dates and working days
- Diagnose Excel errors and handle them with SI.NON.DISP (IFNA), SIERREUR (IFERROR) or an explicit test
Syllabus: 5 lessons
- Match two tables on a key with RECHERCHEX (XLOOKUP)
- Use INDEX and EQUIV (MATCH) to look up in any direction
- Do an approximate lookup in a threshold grid
- 02
Nested SI (IF), SI.CONDITIONS (IFS), ET (AND) and OU (OR)
- Handle more than two cases with nested SI or SI.CONDITIONS
- Combine several conditions with ET and OU
- Organize business rules so they stay readable and editable
- 03
Conditional Sums and Counts: SOMME.SI.ENS (SUMIFS), NB.SI.ENS (COUNTIFS)
- Calculate a sum, a count or an average based on one or more criteria
- Write text, numeric and date criteria, including from cells
- Build a two-way summary table with conditional formulas
- 04
Text and Date Functions
- Clean up and split imported text
- Calculate due dates and durations with date functions
- Display a date or a number in a chosen format inside a text
- 05
Handling Errors: SIERREUR (IFERROR) and SI.NON.DISP (IFNA)
- Recognize the main Excel errors and their cause
- Replace an expected error with a suitable value without hiding real anomalies
- Flag missing data instead of hiding it
Final project
Value and Check a Quarter's Orders
The sales department sends you the product catalog (produits.csv), an order log containing an unknown reference (commandes with C105/P010), the commission grid and the salespeople file. Build a workbook that values each order, flags anomalies, calculates commissions and bonuses according to the business rules, and presents a summary by salesperson and by product, with editable parameters and no hidden errors.
Deliverables
- An .xlsx workbook with the sheets Produits, Commandes, Grille, Commerciaux and Synthese
- Order valuation (total 1323) with a check column and an anomaly counter (1)
- Bonus calculation with SI.CONDITIONS (total 6500) and commissions from a threshold grid (total 531)
- A salespeople x products summary with SOMME.SI.ENS whose total is 12100
Assessment criteria
- Accuracy of the numerical results
- Reference ranges as absolute and copyable formulas
- Business rules parameterized in cells, bounds matching the brief
- Errors handled as close as possible to their cause, without blanket SIERREUR
- Readability: labels, explicit sheet names, visible checks
Frequently asked questions
XLOOKUP or VLOOKUP: which one should I learn?
XLOOKUP is simpler and safer, but it only exists in Microsoft 365, Excel 2021 and later versions. The course also covers VLOOKUP and INDEX/MATCH, which you will meet in existing workbooks.
Do I need to have taken Excel Fundamentals?
Yes, or have the equivalent skills: formulas, absolute references and the IF function are used in every lesson.
Do the formulas work in an English version of Excel?
Yes, with the English names given in parentheses (XLOOKUP, SUMIFS, IFERROR…) and the comma as the argument separator instead of the semicolon.
What is the logical next step?
The course on PivotTables, which produces in a few clicks summaries that you can now check with SOMME.SI.ENS (SUMIFS).
This course, told for your job
Say what you want to be able to do: “Excel: Lookup and Logical Functions” takes its place in your path, with examples from your field. The concepts stay those of the course.