Google Sheets Advanced Lookup Functions Questions and Answers 1 — 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?
- =VLOOKUP("North", B2:E100, 4, FALSE)
- =FILTER(A2:E100, B2:B100="North", E2:E100>50000)
- =QUERY(A2:E100, "SELECT * WHERE B = 'North' AND E > 50000") (Correct answer)
- =INDEX(A2:E100, MATCH("North", B2:B100, 0), 5)
Correct answer: =QUERY(A2:E100, "SELECT * WHERE B = 'North' AND E > 50000")
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?
- XLOOKUP can only perform approximate matches, making it faster.
- XLOOKUP requires the lookup column to be the first column in the specified range.
- XLOOKUP can look up values in columns to the left of the lookup column and defaults to an exact match. (Correct answer)
- XLOOKUP has a simpler syntax with fewer arguments than VLOOKUP.
Correct answer: XLOOKUP can look up values in columns to the left of the lookup column and defaults to an exact match.
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?
- VLOOKUP
- HLOOKUP
- MATCH
- INDEX (Correct answer)
Correct answer: INDEX
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?
- VLOOKUP and HLOOKUP
- INDEX and MATCH (Correct answer)
- QUERY and FILTER
- SUMIF and COUNTIF
Correct answer: INDEX and MATCH
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?
- =FILTER(B2:B500, C2:C500="Sales")
- =VLOOKUP("Sales", C2:B500, 2, FALSE)
- =QUERY(A2:D500, "SELECT B WHERE C = 'Sales'")
- Both A and C (Correct answer)
Correct answer: Both A and C
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.
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?
- By using their header names (e.g., 'Sales', 'Region')
- By using R1C1 notation (e.g., C1, C2)
- By using their column letter identifiers (e.g., A, B, C) (Correct answer)
- By using their numerical index (e.g., 1, 2, 3)
Correct answer: By using their column letter identifiers (e.g., A, B, C)
The Google Visualization API Query Language, used by the QUERY function, refers to columns by their letter identifiers (A, B, C, etc.) when the `data` argument is a direct range reference. For example, to select the first and third columns from the range A1:F50, the clause would be `SELECT A, C`.
Google Sheets Advanced Lookup Functions Questions and Answers 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?