Google Sheets Advanced Lookup Functions Questions and Answers

Question 1

A financial analyst has a large dataset of quarterly sales figures in a Google Sheet (range A2:E100), with 'Quarter' in column A, 'Region' in column B, 'Product' in column C, 'Units Sold' in column D, and 'Total Revenue' in column E. They need to extract all records for the 'North' region where 'Total Revenue' was greater than $50,000. Which of the following formulas is the most appropriate and efficient for this task?

Accepted Answer

=QUERY(A2:E100, "SELECT * WHERE B = 'North' AND E > 50000")

Answer

The QUERY function is the most suitable for this scenario as it allows for complex filtering using SQL-like language. It can handle multiple conditions ('Region' is 'North' AND 'Total Revenue' > 50000) within a single, concise formula. While FILTER can also work, QUERY is often more powerful for complex data extraction and analysis. VLOOKUP and INDEX/MATCH are designed to find a single value, not extract multiple rows of data that meet criteria.

Question 2

Which of the following is a key advantage of using XLOOKUP over the traditional VLOOKUP function in Google Sheets?

Accepted Answer

XLOOKUP can look up values in columns to the left of the lookup column and defaults to an exact match.

Answer

A major limitation of VLOOKUP is its inability to look to the left; the lookup column must be the first one in the table array. XLOOKUP overcomes this, allowing the lookup and return columns to be anywhere. Additionally, XLOOKUP defaults to an exact match, which is safer and more commonly needed than VLOOKUP's default approximate match.

Question 3

You are building a dynamic dashboard and need to retrieve a specific value from a data matrix (A1:G20). You know the exact row number (5) and column number (3) of the cell you want to pull data from. Which function is designed specifically to return a value from a reference, given the row and column numbers?

Accepted Answer

INDEX

Answer

The INDEX function is used to return the value of a cell at a specified row and column offset within a given range. For example, =INDEX(A1:G20, 5, 3) would return the value from cell C5. MATCH is used to find the position of a value, while VLOOKUP and HLOOKUP search for a value and return a corresponding item from a different column or row.

Question 4

To perform a two-way lookup (based on both a row and a column criterion) in Google Sheets, a powerful and flexible combination of functions is often used. Which pair of functions is most commonly combined for this purpose?

Accepted Answer

INDEX and MATCH

Answer

The combination of INDEX and MATCH is a standard and highly flexible method for performing two-way lookups. You can use one MATCH function to find the correct row based on a criterion and a second MATCH function to find the correct column. The INDEX function then uses these row and column numbers to pinpoint the exact cell value to return.

Question 5

A user wants to create a list of all employees in the 'Sales' department from a master employee list in the range A2:D500. The department is listed in column C. Which formula will dynamically spill all matching employee names from column B?

Accepted Answer

Both A and C

Answer

Both the FILTER and QUERY functions are capable of solving this problem effectively. The FILTER function is designed to filter a range based on specified criteria and will return all matching results. The QUERY function can also select the specific column (B) where the condition (column C equals 'Sales') is met. Both are valid and widely used methods for this task.

Google Sheets Test Practice Test

Google Sheets Test Practice Test

Google Sheets Advanced Lookup Functions Questions and Answers