Lookup Like a Pro

When working with data, it’s common to find it split across different tables or sheets. Often, we need to bring information from one place to another to get a complete picture. Manually searching through thousands of rows to find a specific piece of data is not only tedious, but also prone to errors. But what if we could automate that process? What if we could tell our spreadsheet to find and fetch the exact information we need, all by itself?

That’s exactly what we’ll learn to do with lookup functions. These powerful tools act like a personal assistant, helping us quickly find and retrieve specific data points from large datasets without any manual searching.

Let’s begin with one of the most commonly used lookup functions in Google Sheets: VLOOKUP().

The VLOOKUP() function

VLOOKUP() is a vertical search tool. It looks for a value in the first column of the data and grabs related information from the same row in another column when it finds it. This tool is great for adding product details to a sales report, or finding customer information from a main list.

It takes four parameters, as mentioned below.

• search_key: The value we are looking for.

• range: The block of data we want to search in.

• index: The column number within our range that contains the data we want to retrieve.

• is_sorted (Optional): Determines whether the function looks for an exact or approximate match. The value of is_sorted can be either TRUE or FALSE.

  • FALSE: Exact match. The function returns a result only if it finds the exact search value.

  • TRUE (Default): Approximate match. The function returns the largest value less than or equal to the search key. For example, if we search for 10:

    • In the list 9, 11, it returns 9.

    • In the list 9, 10, 11, it returns 10.

Suppose we have two separate sheets:

• A Product catalog sheet that contains detailed information about each product, including the ProductID, Product Name, and Unit Price.

• A Sales data sheet that lists every sale by Order ID, ProductID, and Quantity Sold.

Our goal is to figure out the total revenue for each sale. We can’t do this with just our sales data because it’s missing the price of each product. This is the work of VLOOKUP. We’ll use the ProductID as our unique identifier to find the Unit Price in the Product catalog sheet, and bring it back to our Sales data sheet.

The following Product catalog sheet acts as our main list, containing the details we need to look up:

VLOOKUP() requires the lookup value (in this case, ProductID) to be in the first column of the data range.

Next, in the following Sales data sheet, we’ll perform the lookup to pull in the Unit price from the Product catalog sheet.

In this sheet, we’ll use a VLOOKUP() formula in cell E3 to find the price for ProductID 1001. Type the formula =VLOOKUP(D3, 'Product catalog'!B:D, 3, FALSE) in ...