Intermediate Excel Skills Test: Assess Workplace Readiness

In the function =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), what value must be passed to guarantee an exact match?

In cell C2, evaluate if the sales rep Met Target. If Actual Sales (B2) is greater than or equal to 50000, return "Bonus", otherwise return "No Bonus".

Which of the following Excel functions support conditional aggregation based on one or more criteria? Select all that apply.

In target cell E2, calculate the total revenue generated exclusively by the "Tech" category using the SUMIF function.

Which data structure is required to ensure a Pivot Table functions properly without errors?

In cell C6, count how many employees have an evaluation score of 80 or higher using COUNTIF.

What is a primary architectural advantage of using INDEX & MATCH instead of traditional VLOOKUP?

In cell C2, combine First Name (A2) and Last Name (B2) with a space in between.

What does the formula =IFERROR(10/0, "Calculated") return in Excel?

In cell E2, wrap a VLOOKUP searching for Product ID 999 so that if it is not found, it returns "Not Found" instead of #N/A.

Which function extracts the first 3 characters from the text string in cell A1?

In target cell B2, extract the 3-digit country code prefix from the phone number in A2 using the LEFT function.

Which of the following statements about Excel logical functions AND and OR are true? (Select all that apply)

In target cell E2, calculate the average sales amount for the "West" region using AVERAGEIF.

What does the TRIM function do to a text string in Excel?

In target cell B2, clean the text in A2 by removing extraneous leading and trailing spaces using TRIM.

What does the formula =MATCH("Banana", {"Apple", "Banana", "Cherry"}, 0) return?

In target cell B2, calculate the total character count of the serial number in A2 using the LEN function.

Which of the following advantages are provided when converting a standard range into an official Excel Table? (Select all that apply)

In target cell B2, convert the client name in A2 to all uppercase letters using the UPPER function.

When constructing a nested IF statement in Excel, what happens if none of the logical conditions evaluate to TRUE and no false argument is specified?

In target cell C2, assign commission tier: If Sales (B2) is > 10000 return 0.1, otherwise if Sales is > 5000 return 0.05, else return 0.

When using VLOOKUP with approximate match (TRUE or omitted), what crucial prerequisite must the table array meet?

In target cell D2, count how many orders are in the "East" region AND have an "Active" status using COUNTIFS.

Which of the following can trigger automated cell formatting using Excel Conditional Formatting? (Select all that apply)

In target cell B2, extract the 4-digit year suffix from the code in A2 using the RIGHT function.

Which Excel feature restricts user input in a cell to a predefined dropdown list of acceptable options?

In target cell B2, extract 3 characters starting from position 5 in cell A2 using the MID function.

When using the 'Remove Duplicates' feature on multiple selected columns, how does Excel determine whether a row is a duplicate?

In target cell D2, retrieve the price for "Widget B" from table A2:B4 using INDEX and MATCH.