Google Sheets Formula to Search in a Sheet and Return Value from Another

Formula Overview

To search for a value in one column (or sheet) and return a corresponding value from another column (or sheet) in Google Sheets, the VLOOKUP function is commonly used. This function allows you to look up a value in the leftmost column of a range and return a value in the same row from a specified column.

Formula Syntax

The syntax for the VLOOKUP function is as follows:

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

Parameters:

• search_key: The value you want to search for.
• range: The range of cells where the search will take place. The first column in this range is where it will look for the search_key.
• index: The column number in the range from which to retrieve the value. This is indexed starting with 1.
• is_sorted: [Optional] A boolean value indicating whether the range is sorted. Use FALSE for an exact match.

Example Formula

Suppose you have the following structure:

• Sheet1 where you want to search for a specific product ID in column A.
• Sheet2 where the product IDs are also in column A, and their corresponding prices are in column B.

The formula to find the price of a product ID entered in cell A2 of Sheet1 based on the product IDs listed in Sheet2 would be:

=VLOOKUP(A2, Sheet2!A:B, 2, FALSE)

Explanation

• A2: This is the cell in Sheet1 where you have the product ID you want to look up.
• Sheet2!A:B: This specifies the range in Sheet2 that includes both the product IDs and prices.
• 2: This indicates that the required value (price) is in the second column of the specified range.
• FALSE: This ensures that only exact matches are considered.

Practical Illustration

Scenario:

You want to find the price of product ID "P123" located in Sheet1.

1. In Sheet1, Cell A2: Enter P123.
2. In Sheet1, Cell B2: Enter the formula:

   =VLOOKUP(A2, Sheet2!A:B, 2, FALSE)

3. Outcome: If "P123" is found in Sheet2, then the corresponding price from column B will be displayed in cell B2 of Sheet1.

Conclusion

The VLOOKUP function is an essential tool in Google Sheets for searching data across sheets and returning corresponding values. Understanding its syntax and parameters will greatly enhance data management and analysis capabilities within Google Sheets. For more advanced techniques in data analysis, consider exploring the courses offered by the Enterprise DNA Platform.