MCQs on Advanced Formulas and Functions | Excel MCQ Questions
Mastering advanced formulas and functions in Excel is crucial for performing complex data analysis and calculations. From lookup functions like VLOOKUP and XLOOKUP to powerful logical functions like IFS and SWITCH, this chapter covers a range of advanced tools. Learn about array formulas, dynamic arrays, and working with date/time functions to boost your Excel skills and efficiency. These 30 multiple-choice questions will help you test and improve your knowledge on these advanced topics.
MCQs on Lookup Functions (VLOOKUP, HLOOKUP, XLOOKUP)
- What does the VLOOKUP function do in Excel?
a) Looks for a value in the first column of a table and returns a value from the same row
b) Looks for a value in the last column of a table and returns a value from the same row
c) Looks for a value in the first row of a table and returns a value from the same column
d) Looks for a value in a specific cell and returns a value from a different worksheet - Which of the following is a required argument for the VLOOKUP function?
a) Lookup value
b) Lookup array
c) Number of rows to skip
d) All of the above - Which function should you use to look up values both vertically and horizontally?
a) VLOOKUP
b) HLOOKUP
c) XLOOKUP
d) MATCH - Which argument does XLOOKUP replace in VLOOKUP and HLOOKUP?
a) Column index number
b) Search value
c) Search array
d) Lookup array - What will the XLOOKUP function return if it doesn’t find a match, by default?
a) 0
b) #N/A
c) Blank cell
d) Custom error message
MCQs on INDEX and MATCH Functions
- What is the advantage of using INDEX and MATCH over VLOOKUP in Excel?
a) INDEX and MATCH can search both vertically and horizontally
b) INDEX and MATCH are faster than VLOOKUP
c) INDEX and MATCH can look to the left of the lookup value
d) All of the above - In the INDEX function, which argument specifies the array of data?
a) Row_num
b) Column_num
c) Array
d) Lookup_value - What does the MATCH function return in Excel?
a) A value from a specific range
b) The position of a lookup value in a range
c) The sum of values in a range
d) The highest value in a range - When combining INDEX and MATCH, which function should be used to locate the row number?
a) INDEX
b) MATCH
c) VLOOKUP
d) COUNTIF - Which of the following is TRUE about the INDEX and MATCH combination?
a) It only works for horizontal lookups
b) MATCH must always come before INDEX
c) INDEX returns the value based on row and column number
d) Both MATCH and INDEX functions are not necessary
MCQs on Advanced Logical Functions (IFS, SWITCH)
- What does the IFS function allow you to do in Excel?
a) Perform multiple calculations based on conditions
b) Execute a single logical test
c) Compare multiple values
d) None of the above - Which of the following is a feature of the SWITCH function in Excel?
a) It evaluates multiple conditions sequentially
b) It only evaluates logical tests
c) It is a replacement for IF statements only
d) It works only for date values - What is the first argument in the IFS function?
a) Logical test
b) Value to return if the condition is TRUE
c) Condition to test
d) Number of arguments - Which Excel function would be more efficient when testing multiple conditions?
a) IFS
b) IF
c) SWITCH
d) COUNTIF - What happens when none of the conditions in the IFS function are TRUE?
a) It returns an error
b) It returns a blank cell
c) It returns the final argument as the default
d) It stops the formula
MCQs on Array Formulas and Dynamic Arrays
- What does an array formula in Excel do?
a) Performs multiple calculations on a range of data
b) Allows input for large datasets only
c) Works with one cell at a time
d) Increases workbook performance - How do you enter an array formula in Excel?
a) Press Enter after typing the formula
b) Press Ctrl + Shift + Enter
c) Click the array button in the Ribbon
d) Press F2 to edit - Which of the following is an example of a dynamic array function in Excel?
a) SUMIFS
b) TRANSPOSE
c) FILTER
d) COUNTIF - What does the FILTER function do in Excel?
a) Filters the data based on multiple conditions
b) Returns unique values from a list
c) Sorts data based on custom criteria
d) Removes duplicate values from data - Which of the following is TRUE about dynamic arrays in Excel?
a) They return only a single value
b) They require Ctrl + Shift + Enter to function
c) They automatically spill over multiple cells if needed
d) They do not work with ranges of data
MCQs on Working with Date and Time Functions
- Which Excel function is used to get the current date?
a) DATE()
b) NOW()
c) TODAY()
d) CURRENTDATE() - How can you extract the year from a date in Excel?
a) YEAR()
b) DATEPART()
c) TEXT()
d) EXTRACTYEAR() - Which function would you use to calculate the difference between two dates in Excel?
a) DATEDIF()
b) TIMEDIFF()
c) DATE()
d) TIME() - What does the NOW() function return in Excel?
a) Current date and time
b) Only current time
c) Only current date
d) Current date, time, and weekday - What is the result of the formula
=TEXT(A1,"dd/mm/yyyy")if cell A1 contains 01/01/2024?
a) 01/01/2024
b) 2024/01/01
c) January 1, 2024
d) 01-01-2024
MCQs on Date and Time Operations
- Which function returns the number of days between two dates?
a) DAYS()
b) DATEDIF()
c) DATE()
d) DAY() - What is the purpose of the EDATE function in Excel?
a) Adds a specified number of months to a date
b) Returns the number of days between two dates
c) Returns the weekday name
d) Subtracts days from a date - How would you convert a date stored as text into a proper date format?
a) Use the DATEVALUE() function
b) Use the TEXT() function
c) Use the VALUE() function
d) Use the DAY() function - Which of the following functions would return the weekday of a date?
a) WEEKDAY()
b) DAY()
c) WORKDAY()
d) YEAR() - What does the WORKDAY function in Excel return?
a) The next working day based on a specified start date
b) The number of working days between two dates
c) The weekday for a specific date
d) The total number of work hours
Answer Key
| Qno | Answer |
|---|---|
| 1 | a) Looks for a value in the first column of a table and returns a value from the same row |
| 2 | a) Lookup value |
| 3 | c) XLOOKUP |
| 4 | a) Column index number |
| 5 | c) Blank cell |
| 6 | d) All of the above |
| 7 | c) Array |
| 8 | b) The position of a lookup value in a range |
| 9 | b) MATCH |
| 10 | c) INDEX returns the value based on row and column number |
| 11 | a) Perform multiple calculations based on conditions |
| 12 | a) It evaluates multiple conditions sequentially |
| 13 | c) Condition to test |
| 14 | a) IFS |
| 15 | c) It returns the final argument as the default |
| 16 | a) Performs multiple calculations on a range of data |
| 17 | b) Press Ctrl + Shift + Enter |
| 18 | c) FILTER |
| 19 | a) Filters the data based on multiple conditions |
| 20 | c) They automatically spill over multiple cells if needed |
| 21 | c) TODAY() |
| 22 | a) YEAR() |
| 23 | a) DATEDIF() |
| 24 | a) Current date and time |
| 25 | a) 01/01/2024 |
| 26 | a) DAYS() |
| 27 | a) Adds a specified number of months to a date |
| 28 | a) Use the DATEVALUE() function |
| 29 | a) WEEKDAY() |
| 30 | a) The next working day based on a specified start date |