Mastering Google Sheets: Solving Real-World Problems Beyond VLOOKUP

Hi everyone! In this blog post, I will share a question and its solution that I encountered during an interview. This experience enhanced my understanding of the VLOOKUP function in Google Sheets, taking my knowledge from basic to advanced levels.

The Question

The interviewer first asked each interviewee, "Do you know how to use the VLOOKUP function?" Then, they showed us sample data (similar to what I have provided in the image above) and asked us to fetch the Aadhar numbers of employees in Sheet2 from Sheet1.

Based on the start of the question, every interviewee, including me, thought this problem could be solved using the VLOOKUP function and began implementing it without examining the data carefully. However, everyone encountered errors and got rejected from the interview.

I have a coding background but had never used Excel before working in this company as a Senior Assistant. After joining, I started working in Google Sheets and explored features that interested me, like Apps Script and some basics of Google Sheets. That day, I was applying for the role of Data Entry Operator (DEO).

Before this interview, I knew how to use the VLOOKUP function, but only at a basic level and with simple, straightforward data. Naturally, I also got rejected. Afterward, I decided to explore the function deeply, and this blog post is the result of my learning.

Understanding the Problem

What do you think? Can this problem be solved using the VLOOKUP function? The answer is NO. This problem cannot be solved using VLOOKUP because the matching column comes after the column from which data needs to be fetched.

The VLOOKUP Function

The VLOOKUP function is used to search for a key in the first column of a provided range and return data from one of the columns after it in the matching row. By default, it searches only the first column of the range, and this behavior cannot be changed (e.g., matching in the second column instead).

Syntax: =VLOOKUP(search_key, range, index, [is_sorted])

Arguments:-

• search_key: The value to search for in the first column of the range.
• range: The range where the search is performed. Matching occurs in the first column, and data is retrieved from one of the columns after it.
• index: The relative column index (starting from 1) of the data to retrieve. For example, if the range is B:E and the index is 3, the data will be returned from column D.
• is_sorted (optional): A Boolean value (TRUE or FALSE). By default, it is TRUE. TRUE: Allows approximate matching (use only when the data is sorted). FALSE: Ensures exact matching, regardless of whether the data is sorted.

Note: Use the is_sorted argument carefully, as omitting it may lead to unexpected results. When set to TRUE or omitted, the function assumes the data is sorted and returns the closest match (less than or equal to the lookup value).

The Sorted Sample Data

The sample data below was sorted, so let’s see how it works for different scenarios.

Scenario 1: sorted = TRUE (or omitted)

Let's say we want to look up the Score for ID 102. We'll use the following formula:

=VLOOKUP(102, A2:C5, 3, TRUE)

Result: The formula returns 90 because the ID 102 exactly matches, and since the range is sorted, Google Sheets finds the closest match.

Scenario 2: sorted = FALSE (Exact match only)

Now, let's search for ID 105 with the sorted parameter set to FALSE:

=VLOOKUP(105, A2:C5, 3, FALSE)

Result: Since there is no ID 105, the formula returns #N/A because exact matches are required.

Scenario 3: sorted = TRUE (Approximate match)

If you use sorted = TRUE for an ID that doesn’t exist, like 106, Google Sheets will look for the closest match:

=VLOOKUP(106, A2:C5, 3, TRUE)

Result: The formula returns 92, which is the score for ID 104, as it is the closest ID less than 106 in the sorted list.

The Unsorted Sample Data

Now see different scenarios with unsorted data as provided in below image.

Scenario 1: sorted = TRUE (Data Not Sorted)

Let’s use the same formula as before but here the range is not sorted:

=VLOOKUP(102, A2:C5, 3, TRUE)

Result: In this case, Google Sheets may return incorrect results because the function expects the range to be sorted when TRUE is used. The formula will likely return the wrong value by looking at the nearest smaller value instead of finding the exact match. For example, it might return 85 (from ID 101) as the smaller closest match, even though the data isn't sorted.

Scenario 2: sorted = FALSE (Unsorted Range)

When sorted = FALSE (or omitted), VLOOKUP will search for an exact match regardless of whether the data is sorted.

=VLOOKUP(105, A2:C5, 3, FALSE)

Result: The function will return #N/A because there is no exact match for ID 105 in the unsorted data. If the ID 105 did exist in the data, VLOOKUP would return the corresponding score. For example If the ID 102 was searched with FALSE:

=VLOOKUP(102, A2:C5, 3, FALSE)

Result: It will return 90, as the exact match for ID 102 exists in the unsorted range.

So It is recommended to use FALSE when we want to match exactly whether data is sorted or unsorted and only use TRUE if data is sorted and want to fetch data by approximate matching.

The Solution

Now you understand why VLOOKUP cannot solve this problem. So how can we fetch Aadhar numbers? The answer lies in using the INDEX and MATCH functions together:

=INDEX(Sheet1!B:B, MATCH(A2, Sheet1!C:C, 0))

Here:-

• Sheet1!B:B: Specifies the column containing Aadhar numbers (the values to fetch).
  
• MATCH(A2, Sheet1!C:C, 0): Searches for the name in Sheet1!C:C (the Name column) and returns the row number of an exact match.
• INDEX: Fetches the value from the Aadhar column (Sheet1!B:B) based on the row number returned by MATCH.
  

Lesson Learned

This interview taught me the importance of focusing on the question before jumping to conclusions. Sometimes, questions are designed to divert attention away from the real solution.

Conclusion

I hope you found this blog post informative and learned something valuable about the limitations and advanced usage of the VLOOKUP function, as well as how to solve similar challenges using INDEX and MATCH. Interviews often test not just our knowledge but also our attention to detail and problem-solving skills.

If you have any suggestions, questions, or your own experiences to share, feel free to drop them in the comments. I’d love to hear your thoughts and continue the discussion. Thank you for reading! 😊