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

=VLOOKUP("North", B2:E100, 4, FALSE)

Answer

=FILTER(A2:E100, B2:B100="North", E2:E100>50000)

Answer

=INDEX(A2:E100, MATCH("North", B2:B100, 0), 5)

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

XLOOKUP can only perform approximate matches, making it faster.

Answer

XLOOKUP requires the lookup column to be the first column in the specified range.

Answer

XLOOKUP has a simpler syntax with fewer arguments than VLOOKUP.

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

VLOOKUP

Answer

HLOOKUP

Answer

MATCH

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

VLOOKUP and HLOOKUP

Answer

QUERY and FILTER

Answer

SUMIF and COUNTIF

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

=FILTER(B2:B500, C2:C500="Sales")

Answer

=VLOOKUP("Sales", C2:B500, 2, FALSE)

Answer

=QUERY(A2:D500, "SELECT B WHERE C = 'Sales'")

Question 6

When using the `QUERY` function in Google Sheets, how do you refer to columns within the query string argument when the data range is A1:F50?

Accepted Answer

By using their column letter identifiers (e.g., A, B, C)

Answer

By using their header names (e.g., 'Sales', 'Region')

Answer

By using R1C1 notation (e.g., C1, C2)

Answer

By using their numerical index (e.g., 1, 2, 3)