askvity

How to do VLOOKUP in Google Sheets?

Published in Google Sheets 4 mins read

VLOOKUP in Google Sheets allows you to search for a value in the first column of a range and return a value in the same row from a column you specify. Here's how to use it:

1. Organize Your Data:

Ensure your data is structured in a table format within your Google Sheet. The value you're searching for must be in the first column of the range you'll specify in the VLOOKUP function.

2. Select an Output Cell:

Choose the cell where you want the result of the VLOOKUP function to appear.

3. Enter the VLOOKUP Function:

Start typing the VLOOKUP formula in the selected cell. The basic syntax is:

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

4. Define the search_key:

  • search_key: This is the value you want to find in the first column of your data range. This can be a cell reference (e.g., A2), text in quotes (e.g., "apple"), or a number.

5. Define the range:

  • range: This is the range of cells where your data is located (e.g., A1:C10). The search_key is searched in the first column of this range.

6. Define the index:

  • index: This is the column number within the range that contains the value you want to return. The first column of the range is 1, the second is 2, and so on.

7. Define the is_sorted (Optional but Important):

  • is_sorted: This argument determines whether VLOOKUP performs an approximate or exact match.

    • FALSE (Recommended for Exact Matches): Use FALSE to ensure VLOOKUP only returns a value if it finds an exact match for the search_key. This is the most common and generally recommended use case. If no exact match is found, it will return #N/A.
    • TRUE (Approximate Match): Use TRUE or leave this argument blank if the first column of your range is sorted in ascending order, and you want VLOOKUP to find the closest match. This is generally not recommended unless your data is specifically prepared for it and you understand its implications. It can lead to unexpected results if the data isn't sorted correctly.

8. Execute the Function:

Press Enter to execute the formula. The cell will display the value found in the specified index column that corresponds to the search_key. If VLOOKUP cannot find the search_key (and you're using FALSE for is_sorted), it will return the error #N/A.

Example:

Suppose you have a table with product codes in column A (A1:A10), product names in column B (B1:B10), and prices in column C (C1:C10). You want to find the price of the product with code "1234" (which is in cell E1).

The formula would be:

=VLOOKUP(E1, A1:C10, 3, FALSE)

  • E1 is the search_key (the product code "1234").
  • A1:C10 is the range where the data is located.
  • 3 is the index (the price is in the third column of the range).
  • FALSE specifies that you want an exact match.

Common Issues and Solutions:

  • #N/A Error: The search_key was not found in the first column of the range (when using FALSE for is_sorted), or the data type of the search_key doesn't match the data type in the lookup column (e.g., trying to match text with a number). Ensure your data is accurate and the search key exists.
  • Incorrect Results with TRUE: The first column of the range is not sorted correctly, leading to an incorrect approximate match. Ensure data is sorted in ascending order. Using FALSE for exact matches is almost always preferable.
  • Column Count Issue: The specified index is greater than the number of columns in the range. Double-check your column number.

Related Articles