WorksheetsICHEME - Excel Workshop
Total questions: 7
Worksheet time: 5mins
In the worksheet shown below, you want to use the XLOOKUP function to find the employee whose ID appears in cell E2. Using the data in columns A and B, what formula is needed in cell F2?
Hint: The lookup values are the same. The range for the VLOOKUP includes the entire column, but the XLOOKUP splits the referenced ranges to a range to search and one to find the returned value.
=XLOOKUP(E2,B:B,A:A)
=XLOOKUP(E2,A:A,B:B)
=XLOOKUP(B:B,A:A)
=XLOOKUP(B:B,A:A,E2)
An Excel workbook tracks the sales data for a company. A pivot table is in the adjacent tab and provides a high level view of the sales by department. The sales numbers are updated in the workbook, but the pivot table is not reflecting these changes. What is most likely causing this problem?
You need to create a new pivot table to reflect the changes
There is an error that is preventing the pivot table from updating
You need to refresh the pivot table to include the updated data.
You need to refresh the tab with the raw data to send the changes to the pivot table.
Which of the formulae below contain the correct syntax (formula arguments) for the VLOOKUP function?
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
=VLOOKUP(table_array, lookup_value, col_index_num, range_lookup)
=VLOOKUP(lookup_value, table_array, col_index_num, value)
=VLOOKUP(lookup_value, table_array, row_index_num, range_lookup)
What dialog box allow you to change a field name in a Pivot table?
Field Options
Field Settings
Field Pane
Field Structure
Which areas would you place fields to represent the Pivot Table below?
Filters: Buyer & Amount; Columns: Amount; Rows: N/A; Values: N/A
Filters: N/A; Columns: Buyer; Rows: N/A; Values: Amount
Filters: N/A; Columns: N/A; Rows: Buyer; Values: Amount
Filters: N/A; Columns: Buyer & Amount; Rows: N/A; Values: N/A
Which areas would you place fields to represent the Pivot Table below?
Filters: Type; Columns: Amount; Rows: Buyer; Values: N/A
Filters: N/A; Columns: Type; Rows: Buyer; Values: Amount
Filters: N/A; Columns: N/A; Rows: Buyer & Type; Values: Amount
Filters: N/A; Columns: Buyer & Type; Rows: N/A; Values: Amount
Which is the correct VLOOKUP formula for obtaining the number of apples?
=VLOOKUP(A7, A2:B5, 2, TRUE)
=VLOOKUP(A2, B2:B5, 1, FALSE)
=VLOOKUP(A7, A2:B5, 1, FALSE)
=VLOOKUP(A7, A2:B5, 2, FALSE)
