Advanced Data Analysis Using Excel Functions MCQs

1. Which Excel function is used to look up a value in a table by matching it vertically?
(A) HLOOKUP
(B) VLOOKUP
(C) MATCH
(D) INDEX

2. Which function returns the relative position of an item in a range?
(A) INDEX
(B) FIND
(C) SEARCH
(D) MATCH

3. What is the advantage of using the INDEX-MATCH combination over VLOOKUP?
(A) Allows lookups to the left and is more flexible
(B) Works only with text values
(C) Only works in PivotTables
(D) Used for financial formatting

4. Which function is used to remove duplicate values from a range in Excel 365?
(A) REMOVE
(B) CLEAN
(C) UNIQUE
(D) TRIM

5. What does the TEXT function in Excel do?
(A) Converts numbers to text in a specified format
(B) Counts text values in a range
(C) Finds the length of text
(D) Changes text case

6. Which function returns the nth largest value in a dataset?
(A) MAX
(B) INDEX
(C) RANK
(D) LARGE

7. What is the purpose of the IFERROR function in Excel?
(A) To display a custom result when a formula results in an error
(B) To count the number of errors in a range
(C) To highlight cells with errors
(D) To repair corrupted Excel files

8. Which function allows performing multiple conditions in Excel?
(A) IF
(B) IFS
(C) SUM
(D) INDEX

9. What does the FILTER function in Excel do?
(A) Sorts numbers in ascending order
(B) Deletes duplicate rows permanently
(C) Filters data dynamically based on given criteria
(D) Hides rows without deleting them

10. Which function is most suitable for performing approximate matches in data analysis?
(A) VLOOKUP with TRUE as range_lookup
(B) MATCH with exact match
(C) INDEX with absolute reference
(D) SUMPRODUCT