MOS Excel: Formulas and Functions 4 — Questions and Answers
Question 1: Which function returns a value from a table based on both row and column positions?
- HLOOKUP
- VLOOKUP
- INDEX (Correct answer)
- MATCH
Correct answer: INDEX
INDEX returns a value from a table by specifying a row number and column number within the range.
Question 2: What does =MOD(17, 5) return?
- 3
- 2 (Correct answer)
- 3.4
- 12
Correct answer: 2
MOD returns the remainder after division; 17 divided by 5 is 3 remainder 2, so MOD(17,5) = 2.
Question 3: Which function counts only the cells in a range that contain numbers?
- COUNTA
- COUNT (Correct answer)
- COUNTBLANK
- COUNTIF
Correct answer: COUNT
COUNT counts only numeric values, while COUNTA counts all non-empty cells regardless of data type.
Question 4: You want to look up a value in column A and return a corresponding value from column C without using VLOOKUP. Which combination works best?
- HLOOKUP + OFFSET
- INDEX + MATCH (Correct answer)
- SUMIF + COUNTIF
- LEFT + RIGHT
Correct answer: INDEX + MATCH
INDEX(range, MATCH(lookup_value, lookup_column, 0)) is a flexible alternative to VLOOKUP that can look left and isn't limited to the first column.
Question 5: What does the TODAY() function return?
- The current date and time
- The current date only (Correct answer)
- The current time only
- The number of days since 1900
Correct answer: The current date only
TODAY() returns only the current date as a serial number formatted as a date, with no time component.
Question 6: Which function should you use to remove extra spaces from the beginning, end, and middle of a text string (leaving only single spaces between words)?
- CLEAN
- TRIM (Correct answer)
- SUBSTITUTE
- REPLACE
Correct answer: TRIM
TRIM removes leading and trailing spaces and reduces multiple internal spaces to a single space.
Question 7: In a nested IF formula =IF(A1>90,"A",IF(A1>80,"B","C")), what is returned when A1 is 85?
- A
- B (Correct answer)
- C
- FALSE
Correct answer: B
85 is not greater than 90 (outer IF fails), but 85 is greater than 80 (inner IF passes), so "B" is returned.
MOS Excel: Formulas and Functions 4
Which function returns a value from a table based on both row and column positions?