Advanced Excel - VLOOKUP, INDEX-MATCH, Pivot Tables | MCQs
1. What does the VLOOKUP function do in Excel?
a) Searches for a value in the first row of a table
b) Searches for a value in the first column of a table and returns a corresponding value
c) Finds the maximum value in a table
d) Returns the last value in a row
Answer: b) Searches for a value in the first column of a table and returns a corresponding value
2. In VLOOKUP, what does the fourth argument (range_lookup) control?
a) The column index number
b) Whether to search for an exact or approximate match
c) The row number
d) The data type of the returned value
Answer: b) Whether to search for an exact or approximate match
3. What happens if VLOOKUP does not find a match?
a) Returns 0
b) Returns #N/A
c) Returns NULL
d) Displays an error message
Answer: b) Returns #N/A
4. Which column number should be specified in VLOOKUP to return data from the second column?
a) 1
b) 2
c) 0
d) Any column number
Answer: b) 2
5. What is the primary limitation of VLOOKUP?
a) It only works with numeric data
b) It searches only from left to right
c) It cannot be used in large datasets
d) It is case-sensitive
Answer: b) It searches only from left to right
6. What does the INDEX function do in Excel?
a) Returns a value at a specific row and column in a range
b) Finds and replaces text
c) Sorts data in ascending order
d) Returns the maximum value in a column
Answer: a) Returns a value at a specific row and column in a range
7. What is the main advantage of INDEX-MATCH over VLOOKUP?
a) It works only with numeric data
b) It allows searching in both directions (left and right)
c) It is case-sensitive
d) It only works with sorted data
Answer: b) It allows searching in both directions (left and right)
8. Which function is used to find the row number of a value in a column?
a) INDEX
b) MATCH
c) VLOOKUP
d) FIND
Answer: b) MATCH
9. What happens if MATCH does not find a value?
a) Returns 0
b) Returns #N/A
c) Returns NULL
d) Displays an error message
Answer: b) Returns #N/A
10. How does INDEX-MATCH improve performance over VLOOKUP?
a) It allows searching from right to left
b) It handles large datasets more efficiently
c) It is more flexible in searching
d) All of the above
Answer: d) All of the above
11. What is the purpose of a Pivot Table in Excel?
a) To find and replace values
b) To sort and filter data
c) To summarize large amounts of data dynamically
d) To create charts
Answer: c) To summarize large amounts of data dynamically
12. Which of the following elements is NOT part of a Pivot Table?
a) Rows
b) Columns
c) Filters
d) Formulas
Answer: d) Formulas
13. Which feature allows you to filter data inside a Pivot Table?
a) Slicers
b) Data Validation
c) Conditional Formatting
d) Sorting
Answer: a) Slicers
14. What happens when you refresh a Pivot Table?
a) The table is deleted
b) The data updates based on the source range
c) The formulas change
d) Nothing happens
Answer: b) The data updates based on the source range
15. What is a calculated field in a Pivot Table?
a) A manually entered field
b) A new field created using formulas
c) A row label
d) A formatting option
Answer: b) A new field created using formulas
16. What is the default calculation in a Pivot Table when you add a numeric field?
a) Average
b) Sum
c) Count
d) Maximum
Answer: b) Sum
17. How do you change the value calculation in a Pivot Table?
a) Right-click the value field and choose "Summarize Values By"
b) Use the Sort function
c) Apply Conditional Formatting
d) Use Find and Replace
Answer: a) Right-click the value field and choose "Summarize Values By"
18. Which feature allows you to drill down into data in a Pivot Table?
a) Power Query
b) Double-clicking a value
c) Using Goal Seek
d) Applying filters
Answer: b) Double-clicking a value
19. Can a Pivot Table be created using external data sources?
a) No, only internal Excel data is allowed
b) Yes, using Power Query or external connections
c) No, Pivot Tables are limited to internal tables
d) Yes, but only with CSV files
Answer: b) Yes, using Power Query or external connections
20. What is the "Refresh" option used for in Pivot Tables?
a) To reset filters
b) To update data from the source
c) To remove blank rows
d) To delete duplicate records
Answer: b) To update data from the source
21. What happens if the column index number in VLOOKUP is greater than the number of columns in the table?
a) Returns #N/A
b) Returns 0
c) Returns NULL
d) Displays an error message
Answer: a) Returns #N/A
22. Can VLOOKUP return multiple values?
a) No, it only returns one value
b) Yes, using an array formula
c) Yes, using SUMIF
d) No, it only works in a single row
Answer: b) Yes, using an array formula
23. How do you make VLOOKUP search from right to left?
a) Use HLOOKUP instead
b) Use INDEX-MATCH instead of VLOOKUP
c) Reverse the column order
d) Use INDIRECT function
Answer: b) Use INDEX-MATCH instead of VLOOKUP
24. What is the purpose of the IFERROR function when combined with VLOOKUP?
a) To sort data
b) To handle errors and return a custom message instead of #N/A
c) To format values as text
d) To round numbers
Answer: b) To handle errors and return a custom message instead of #N/A
25. Which of the following functions is more efficient than VLOOKUP for large datasets?
a) INDEX-MATCH
b) SUMIF
c) CONCATENATE
d) LEFT function
Answer: a) INDEX-MATCH
26. What is the benefit of using a Pivot Chart with a Pivot Table?
a) To visually represent summarized data
b) To convert text to numbers
c) To remove duplicates
d) To apply data validation
Answer: a) To visually represent summarized data
27. Which function helps to group dates in a Pivot Table?
a) DATE()
b) TEXT()
c) Group feature in Pivot Table
d) CONCATENATE
Answer: c) Group feature in Pivot Table
28. What happens if you add a field to both Rows and Values in a Pivot Table?
a) It creates a duplicate entry
b) It displays a sum/count in Values
c) It removes the field
d) It merges the rows
Answer: b) It displays a sum/count in Values
29. Can you create a Pivot Table from multiple sheets?
a) Yes, using Power Query
b) No, Excel does not support this
c) Yes, but only in Excel 2016 and later
d) No, unless VBA is used
Answer: a) Yes, using Power Query
30. What is a "calculated item" in a Pivot Table?
a) A custom column
b) A formula applied to specific items in a field
c) A row filter
d) A method to remove duplicates
Answer: b) A formula applied to specific items in a field
31. Can Pivot Tables automatically update when new data is added?
a) No, they need to be refreshed manually
b) Yes, they update automatically
c) Only if connected to Power BI
d) Only in Excel 365
Answer: a) No, they need to be refreshed manually
32. What happens if VLOOKUP finds multiple matches?
a) It returns all matches
b) It returns the first match only
c) It returns an error
d) It asks the user to select one
Answer: b) It returns the first match only
33. What wildcard character can be used in VLOOKUP for partial matches?
a) *
b) #
c) $
d) @
Answer: a) *
34. How can you make VLOOKUP case-sensitive?
a) Use an additional IF function
b) Combine with EXACT function
c) Use UPPER function
d) VLOOKUP is always case-sensitive
Answer: b) Combine with EXACT function
35. What is the best alternative to VLOOKUP for improved speed in large datasets?
a) HLOOKUP
b) INDEX-MATCH
c) CONCATENATE
d) FIND
Answer: b) INDEX-MATCH
36. What happens if the lookup value is smaller than all values in the lookup column in VLOOKUP?
a) Returns the largest value
b) Returns #N/A
c) Returns 0
d) Returns an empty cell
Answer: b) Returns #N/A
37. What happens if the column index number in INDEX is set to 0?
a) Returns an error
b) Returns the entire row
c) Returns the first column
d) Returns the last column
Answer: b) Returns the entire row
38. What does MATCH return if it does not find a match?
a) 0
b) #VALUE!
c) #N/A
d) NULL
Answer: c) #N/A
39. What argument in MATCH specifies an exact match?
a) 1
b) 0
c) -1
d) 2
Answer: b) 0
40. Which function is best for retrieving a value when the lookup column is not the first column?
a) VLOOKUP
b) INDEX-MATCH
c) SUMIF
d) CONCATENATE
Answer: b) INDEX-MATCH
41. What is the advantage of MATCH when used inside INDEX?
a) It speeds up data retrieval
b) It allows for two-dimensional lookups
c) It is more flexible than VLOOKUP
d) All of the above
Answer: d) All of the above
42. Which Pivot Table field setting is used to count non-empty values?
a) Count
b) Sum
c) Average
d) Min
Answer: a) Count
43. How can you group data by month in a Pivot Table?
a) Sort the data
b) Use Group by Date
c) Use Conditional Formatting
d) Apply a filter
Answer: b) Use Group by Date
44. What is the main benefit of using a Pivot Chart?
a) Allows dynamic visualization of data
b) Converts text to numbers
c) Creates macros automatically
d) Removes duplicate values
Answer: a) Allows dynamic visualization of data
45. What is the default function for numeric values in Pivot Tables?
a) SUM
b) COUNT
c) AVERAGE
d) MIN
Answer: a) SUM
46. Can Pivot Tables be created from multiple tables?
a) No, only one table at a time
b) Yes, using Power Pivot
c) Only if tables are merged first
d) Yes, but only in Excel Online
Answer: b) Yes, using Power Pivot
