Microsoft Excel Formulas and Functions 2

Question 1

What does VLOOKUP's fourth argument (range_lookup) control?

Accepted Answer

Exact match (FALSE) or approximate match (TRUE)

Answer

Columns to search

Answer

Lookup direction

Answer

Include hidden rows

Question 2

How does the IF function handle nested conditions?

Accepted Answer

Value_if_false can contain another IF for multiple conditions

Answer

Cannot be nested

Answer

Requires special syntax

Answer

Only two levels allowed

Question 3

What is the difference between SUMIF and SUMIFS?

Accepted Answer

SUMIF applies one criterion, SUMIFS applies multiple with AND logic

Answer

SUMIF for text, SUMIFS for numbers

Answer

They are identical

Answer

SUMIFS is older

Question 4

What replaced CONCATENATE and why?

Accepted Answer

CONCAT and TEXTJOIN; they accept ranges and add delimiters

Answer

TEXTSPLIT; it splits text

Answer

SEARCH; it finds patterns

Answer

SUBSTITUTE; it removes text

Question 5

How does INDEX work with a single range?

Accepted Answer

Returns the value at a specified row position

Answer

Indexes alphabetically

Answer

Creates an index of values

Answer

Returns the cell address

Question 6

What are the three common VLOOKUP errors?

Accepted Answer

#N/A (not found), #REF! (column exceeds range), #VALUE! (invalid arguments)

Answer

#NULL!, #NAME?, #CALC!

Answer

#DIV/0!, #NUM!, #SPILL!

Answer

VLOOKUP never errors