Google Sheets - Advanced Formulas
VLOOKUP, INDEX/MATCH, ARRAYFORMULA, QUERY, IMPORTRANGE, pivot tables, named ranges
1Which function searches for a value in the first column of a range and returns a corresponding value from another column?
Which function searches for a value in the first column of a range and returns a corresponding value from another column?
Answer
VLOOKUP (Vertical Lookup) searches for a value in the first column of a data range and returns the corresponding value from a specified column. The syntax is VLOOKUP(search_key, range, column_index, is_sorted). This function is one of the most commonly used for cross-referencing data between different tables in Google Sheets.
2In the formula VLOOKUP(A2, B:D, 3, FALSE), what does the FALSE parameter mean?
In the formula VLOOKUP(A2, B:D, 3, FALSE), what does the FALSE parameter mean?
Answer
The last parameter of VLOOKUP indicates the match type. FALSE means the search must find an exact match. If no exact value is found, the formula returns a #N/A error. With TRUE (or omitted), VLOOKUP uses an approximate match, which requires the data to be sorted in ascending order.
3What is the main limitation of VLOOKUP compared to INDEX/MATCH?
What is the main limitation of VLOOKUP compared to INDEX/MATCH?
Answer
VLOOKUP can only search in the first column of the specified range and return a value to the right. If the lookup column is to the right of the result column, VLOOKUP does not work. INDEX/MATCH does not have this restriction and can search in any direction, making it a more flexible combination.
What is the difference between VLOOKUP and HLOOKUP?
How does the INDEX/MATCH combination work to look up a value?
+17 interview questions
Other Data Analytics interview topics
Google Sheets - Fundamentals
SQL - Fundamentals
SQL - Aggregations and Grouping
SQL - Joins
BigQuery - Fundamentals
Data Cleaning
KPIs and Business Metrics
Descriptive Statistics
Zapier and No-Code Automation
Data Visualization Principles
Python & Pandas - Fundamentals
Google Sheets - Automated Dashboards
SQL - Subqueries and CTEs
SQL - Window Functions
BigQuery - Advanced Features
Data Modeling
Funnel and Conversion Analysis
Cohort and Retention Analysis
Google Tag Manager and Tracking
APIs and Webhooks
dbt - Fundamentals
AB Testing and Applied Statistics
Looker Studio (Google Data Studio)
Power BI - Fundamentals
SQL - Advanced Analytical Queries
dbt - Advanced Features
Power BI - DAX and Advanced Dashboards
Python Analytics - Advanced Analysis and ML
Master Data Analytics for your next interview
Access all questions, flashcards, technical tests, code review exercises and interview simulators.
Start for free