Microsoft Excel Formulas and Functions 2 — Questions and Answers
Question 1: What does VLOOKUP's fourth argument (range_lookup) control?
- Columns to search
- Exact match (FALSE) or approximate match (TRUE) (Correct answer)
- Lookup direction
- Include hidden rows
Correct answer: Exact match (FALSE) or approximate match (TRUE)
FALSE requires exact match; TRUE allows approximate match from sorted data.
Omitting defaults to TRUE (approximate), which returns wrong results from unsorted data. Always specify FALSE for exact matching.
Question 2: How does the IF function handle nested conditions?
- Cannot be nested
- Value_if_false can contain another IF for multiple conditions (Correct answer)
- Requires special syntax
- Only two levels allowed
Correct answer: Value_if_false can contain another IF for multiple conditions
You can nest IF functions by placing another IF as the value_if_false argument.
Up to 64 levels. Alternatives: IFS function, SWITCH for specific values, CHOOSE with index, or VLOOKUP against a table.
Question 3: What is the difference between SUMIF and SUMIFS?
- SUMIF for text, SUMIFS for numbers
- SUMIF applies one criterion, SUMIFS applies multiple with AND logic (Correct answer)
- They are identical
- SUMIFS is older
Correct answer: SUMIF applies one criterion, SUMIFS applies multiple with AND logic
SUMIF handles one condition; SUMIFS evaluates multiple criteria, summing where ALL are met.
Key difference: SUMIF puts sum_range last (optional), SUMIFS puts it first (required). Both support wildcards and comparison operators.
Question 4: What replaced CONCATENATE and why?
- TEXTSPLIT; it splits text
- CONCAT and TEXTJOIN; they accept ranges and add delimiters (Correct answer)
- SEARCH; it finds patterns
- SUBSTITUTE; it removes text
Correct answer: CONCAT and TEXTJOIN; they accept ranges and add delimiters
CONCAT accepts ranges; TEXTJOIN adds delimiters and can skip empty cells.
CONCATENATE joins individual strings only. CONCAT accepts ranges. TEXTJOIN adds a delimiter between items and can skip blanks.
Question 5: How does INDEX work with a single range?
- Indexes alphabetically
- Returns the value at a specified row position (Correct answer)
- Creates an index of values
- Returns the cell address
Correct answer: Returns the value at a specified row position
INDEX returns the value at a given row and optional column position within a range.
INDEX is non-volatile (unlike OFFSET) and returns an actual reference. INDEX/MATCH is preferred over VLOOKUP for flexibility.
Question 6: What are the three common VLOOKUP errors?
- #NULL!, #NAME?, #CALC!
- #N/A (not found), #REF! (column exceeds range), #VALUE! (invalid arguments) (Correct answer)
- #DIV/0!, #NUM!, #SPILL!
- VLOOKUP never errors
Correct answer: #N/A (not found), #REF! (column exceeds range), #VALUE! (invalid arguments)
Each error indicates a different setup problem with the lookup.
#N/A: value not found. #REF!: col_index_num exceeds columns. #VALUE!: col_index_num < 1 or lookup > 255 chars.
Microsoft Excel Formulas and Functions 2
What does VLOOKUP's fourth argument (range_lookup) control?