← Interview & exam prep
Technical interview questions
Excel interview questions
29 questions from beginner to advanced, each with the correct answer and a one-line explanation of why. Study them here, or take them as a timed test.
29 questionsBeginner 11 · Intermediate 10 · Advanced 8Answers + explanations
Beginner 11 questions
1Which VLOOKUP 4th argument forces an EXACT match?
- FALSE (0)
- the word exact
- TRUE
- omit it
- 1
Show answerHide answer
A · FALSE (0) FALSE/0 = exact match; TRUE/1 = approximate and requires sorted data.
2Which reference locks BOTH the row and the column?
- A$1
- A#1
- A1
- $A$1
- $A1
Show answerHide answer
D · $A$1 $A$1 is fully absolute. $A1 and A$1 are mixed (one part locked).
3COUNTA counts…
- Non-empty cells of any type
- Only text
- Blank cells
- Unique values
- Only numbers
Show answerHide answer
A · Non-empty cells of any type COUNTA counts all non-empty cells; COUNT counts only numbers.
4IFERROR(x, y) returns y when…
- x is blank
- always
- x evaluates to an error
- x is 0
- x is FALSE
Show answerHide answer
C · x evaluates to an error IFERROR substitutes y only when x is an error value.
5Which modern function can look to the LEFT of the key, unlike VLOOKUP?
- INDEX only
- CHOOSE
- SUMIF
- HLOOKUP
- XLOOKUP
Show answerHide answer
E · XLOOKUP XLOOKUP searches independently of column position and can return values to the left.
6Which returns the POSITION of a value within a range?
- INDEX
- LOOKUP
- MATCH
- CHOOSE
- OFFSET
Show answerHide answer
C · MATCH MATCH returns the relative position; INDEX returns the value at a position.
7Which spills a list of unique values?
- DISTINCT
- SINGLE
- UNIQUE
- DEDUPE
- REMOVEDUPES
Show answerHide answer
C · UNIQUE UNIQUE is the dynamic-array function; DISTINCT is DAX, not Excel.
8Referencing a spilled range that starts in A2 uses…
- A2~
- A2!
- A2$
- A2#
- A2*
Show answerHide answer
D · A2# The spill operator A2# refers to the whole dynamic array that originates at A2.
9To sum with MULTIPLE criteria you use…
- SUMPRODUCT only
- SUMIFS
- SUMIF
- SUM
- DSUM
Show answerHide answer
B · SUMIFS SUMIFS supports several criteria pairs; SUMIF handles a single condition.
10A cleaner alternative to deeply nested IFs is…
- CHOOSECOLS
- AND only
- MULTIIF
- IFS or SWITCH
- NESTIF
Show answerHide answer
D · IFS or SWITCH IFS (and SWITCH) evaluate multiple conditions without heavy nesting.
11ROUND(2.5, 0) returns…
- 3
- 0
- 2.5
- 2
- an error
Show answerHide answer
A · 3 Excel's ROUND rounds halves away from zero, so 2.5 becomes 3.
Intermediate 10 questions
12XLOOKUP match_mode for 'exact or the NEXT LARGER item' is…
- 1
- TRUE
- -1
- 0
- 2
Show answerHide answer
A · 1 1 = exact or next larger; -1 = exact or next smaller; 0 = exact; 2 = wildcard.
13LET() is used to…
- Define a Table
- Create a macro
- Assign names to intermediate results inside one formula
- Lock cells
- Loop rows
Show answerHide answer
C · Assign names to intermediate results inside one formula LET names sub-expressions, improving readability and avoiding recomputation.
14Which array function returns a SINGLE accumulated value?
- SCAN
- MAP
- REDUCE
- LAMBDA
- BYROW
Show answerHide answer
C · REDUCE REDUCE returns one accumulated result; SCAN returns the array of intermediate accumulations.
15FILTER(range, cond) returns #CALC! when…
- The range is text
- There are too many rows
- No rows match and if_empty is omitted
- It is circular
- The condition is TRUE
Show answerHide answer
C · No rows match and if_empty is omitted With no matches and no if_empty argument, FILTER yields the #CALC! empty-array error.
16SUMPRODUCT is powerful because it can…
- Only sum one column
- Look up to the left
- Create a PivotTable
- Join text
- Multiply arrays element-wise and sum, handling multiple conditions
Show answerHide answer
E · Multiply arrays element-wise and sum, handling multiple conditions SUMPRODUCT does array math and multi-criteria sums without helper columns.
17In a Table, the structured reference [@Sales] means…
- The whole Sales column
- This row's value in the Sales column
- The totals row
- The Sales header
- An absolute reference
Show answerHide answer
B · This row's value in the Sales column [@Column] is the current row's value; [Column] is the entire column.
18TEXTJOIN differs from CONCAT because it can…
- Return numbers
- Do math
- Only join two cells
- Sort text
- Use a delimiter and optionally skip empty cells
Show answerHide answer
E · Use a delimiter and optionally skip empty cells TEXTJOIN adds a delimiter and can ignore blanks; CONCAT just concatenates.
19A key advantage of INDEX/MATCH over VLOOKUP is…
- It survives inserted columns and can look left
- It is always faster on huge data
- It sorts automatically
- It is shorter
- It needs no arguments
Show answerHide answer
A · It survives inserted columns and can look left INDEX/MATCH references columns by position/lookup, so inserting columns won't break it, and it can look left.
20SEQUENCE(3) generates…
- A random number
- The value 3
- Text
- An array {1;2;3}
- A blank
Show answerHide answer
D · An array {1;2;3} SEQUENCE spills an array of consecutive numbers.
21In text criteria, the wildcard for 'any SINGLE character' is…
- %
- ~
- *
- #
- ?
Show answerHide answer
E · ? ? matches one character; * matches any number of characters; ~ escapes a wildcard.
Advanced 8 questions
22A recursive LAMBDA must be…
- Marked volatile
- Written in VBA
- Wrapped in IFERROR
- Placed in a Table
- Named in the Name Manager so it can call itself
Show answerHide answer
E · Named in the Name Manager so it can call itself Recursion needs a defined name so the LAMBDA can reference itself.
23Volatile functions (NOW, RAND, OFFSET) are problematic because they…
- Recalculate on every recalculation, hurting performance
- Are always faster
- Never recalculate
- Only run on open
- Cache their result
Show answerHide answer
A · Recalculate on every recalculation, hurting performance Volatile functions force frequent recalculation across the workbook.
24The implicit intersection operator @ in a formula…
- Creates an error
- Sums a range
- Makes a reference absolute
- Forces a single value (legacy behavior)
- Spills an array
Show answerHide answer
D · Forces a single value (legacy behavior) @ coerces to a single value, reproducing pre-dynamic-array behavior.
25To combine text from a range with a delimiter while skipping blanks, use…
- TEXTJOIN
- CONCATENATE
- CONCAT
- MERGE
- JOIN
Show answerHide answer
A · TEXTJOIN Only TEXTJOIN offers both a delimiter and ignore-empty in one call.
26BYROW(array, LAMBDA(...)) is typically used to…
- Remove duplicates
- Apply a calculation per row and return one result per row
- Merge queries
- Transpose
- Sort each row
Show answerHide answer
B · Apply a calculation per row and return one result per row BYROW maps a LAMBDA across rows, returning a column of per-row results.
27In an array constant, columns and rows are separated by…
- Spaces
- Tabs
- Pipes
- Semicolon for columns, comma for rows
- Comma for columns, semicolon for rows
Show answerHide answer
E · Comma for columns, semicolon for rows {1,2,3} is a row; {1;2;3} is a column — comma separates columns, semicolon separates rows (US locale).
28MMULT performs…
- Matrix multiplication of two arrays
- Text merge
- A lookup
- Rounding
- Multiple IFs
Show answerHide answer
A · Matrix multiplication of two arrays MMULT returns the matrix product of two arrays (with compatible dimensions).
29To return multiple columns from XLOOKUP at once, you…
- Wrap in TEXTJOIN
- Use COUNTIF
- Use VLOOKUP instead
- Give a multi-column return_array so it spills several columns
- Cannot
Show answerHide answer
D · Give a multi-column return_array so it spills several columns XLOOKUP can return an entire row/multiple columns when the return array spans them.
What changed lately? Latest Formulas & Functions news, releases & questions →
More technical interview questions