Functions for Excels
Excel functions for logic and lookups let a sheet make decisions and find related data. IF, AND and OR test conditions, SUMIFS and COUNTIFS aggregate by criteria, and lookup functions such as XLOOKUP and VLOOKUP return a value from another table by matching a key.
Stage: Data Skills · Part of the Excel roadmap · Last reviewed 19 November 2026
What to learn in Functions
- Write nested IF formulas and combine conditions with AND and OR
- Total and count rows that meet several criteria using SUMIFS and COUNTIFS
- Look up a value by key with XLOOKUP and handle a missing match
- Explain why VLOOKUP needs exact match and a left-most key column
- Trap errors with IFERROR without hiding real problems
Functions interview questions
What is the difference between VLOOKUP and XLOOKUP?
VLOOKUP searches the first column of a range and returns a value from a column number to the right, and defaults to approximate match. XLOOKUP takes separate lookup and return ranges, can look left, defaults to exact match and has a built-in not-found argument.
Why can VLOOKUP return a wrong result?
If the last argument is omitted or TRUE, it uses approximate matching and expects sorted data, which can return a nearby value. Use FALSE for exact matches. Inserting columns can also break a hard-coded column index number.
Where Functions fits in the Excel roadmap
Functions belongs to the Data Skills stage (beginner level). Organise and analyse.
Other units in this stage
Back to the full Excel roadmap