Lookup Like a Pro

Why lookups matter in data analysis

In real-world data analysis, information is rarely stored in one neat table. Product details may live in one sheet, sales transactions in another, and customer data in a third. Manually searching through thousands of rows to connect these pieces is slow, error-prone, and unsustainable.

This is where lookup functions come in.

Lookup formulas allow us to:

• Automatically fetch related data

• Connect tables using unique identifiers

• Eliminate manual searching

• Build scalable, analysis-ready spreadsheets

Mastering lookups is a major step toward professional data analysis in Google Sheets.

What is a VLOOKUP?

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.

How to use VLOOKUP in Google Sheets

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.

Example: Using VLOOKUP to calculate revenue

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 cell E3.

This tells VLOOKUP() to look at the value in cell D3 and search for it in the first column (column B) of the range B:D in the Product catalog sheet.

• Range (B:D): This means VLOOKUP() will look for matches only within columns B through D; it ignores any data outside of this block. By rule, VLOOKUP() always searches in the first column of the specified range, so here it checks column B.

• Index (3): Once a match is found, the index number tells VLOOKUP() which column to return, from the range. Here, the index is 3, which corresponds to the third column of the range B:D, that is column D.

The final argument, FALSE, ensures VLOOKUP() only returns an exact match. If no exact match is found, the formula will display #N/A. For example, if ProductID 1001 is found, this formula will return 1200 as the Unit price.

Now we can drag the fill handle (the small blue circle in the bottom-right corner of cell E3) down to apply the formula to the rest of the cells in column E. The formula will automatically update for each row, retrieving the correct Unit Price for each ProductID.

Finally, to complete the analysis, we can calculate the total revenue in column F. In cell F3, type the formula =C3 * E3 and drag it down. This will multiply the Quantity Sold by the Unit Price for each order.

What is a HLOOKUP()?

Sometimes, our data is set up differently. Instead of having unique IDs in the first column, they might be in the first row, with data going across rows. Searching through hundreds of columns by hand can take a lot of time, and lead to mistakes. That’s where HLOOKUP() comes in. HLOOKUP() helps us find values when our data is arranged with headers in a row instead of a column.

Just like VLOOKUP, HLOOKUP acts as a search engine, but it searches horizontally across the first row of a data range until it finds our search_key. Once it finds the key, it moves down to a specified row to pull the correct information.

It takes four parameters to work correctly, as mentioned below.

• search_key: The value we are looking for.

• range: The block of data to search in. The search_key must be in the very first row of this range.

• index: The row number within our range that contains the data we want to retrieve. The first row is 1, the second is 2, and so on.

• is_sorted (Optional): Determines whether to look for an exact match (FALSE) or an approximate match (TRUE).

Suppose we have a table showing a company’s sales figures for different departments across the year, with the months listed in the first row of the table. We want to find the sales for the Marketing department in the month of Feb. To do this, we can use the formula: =HLOOKUP("Feb", I3:L6, 2, FALSE)

This tells HLOOKUP to look for the value Feb in the first row of the range I3:L6. Once it finds Feb, it moves down to the second row of that column to get the corresponding value. The last argument, FALSE, ensures that the formula looks for an exact match of Feb. Typing this formula into a cell will return the value 14500, which is the sales figure for Marketing in Feb.

VLOOKUP vs HLOOKUP

Advanced lookups

So far, we’ve seen how VLOOKUP and HLOOKUP can retrieve data, but they both have a major limitation: they can only search in the first column or first row of a range. What if our unique identifier isn’t in that first position? What if we need to search from right-to-left? This is where the powerful combination of INDEX() and MATCH() comes in.

Think of it like this: MATCH is our “data detective” that finds the exact location of a value in a list, and INDEX is our “data retriever” that fetches the data from that location. By combining these two, we can perform lookups in any direction, making our formulas much more flexible and robust.

Let’s look at each function on its own before we combine them.

The MATCH() function

This function searches for a value in a one-dimensional range (a single row or a single column) and returns its position.

• search_key: The value we’re looking for.

• range: The single column or row where we want to search.

• match_type (Optional): When we use 0, it tells the function to look for an exact match. Other values (1 or -1) are for approximate matches and require the data to be sorted, as highlighted below.

  • 1: Finds the largest value that’s less than or equal to the search key. This requires the lookup data to be sorted in an ascending order.

  • -1: Finds the smallest value that’s greater than or equal to the search key. This requires the lookup data to be sorted in a descending order.

For example, =MATCH("Keyboard", A2:A6, 0) will return 3 if Keyboard is the third item in that list.

The INDEX() function

This function returns the value of a cell at a specific position within a range.

• reference: The range of cells that contains the data we want to retrieve.

• row_num: The row number of the value we want.

• column_num (Optional): We only need to include it when our reference is a range with multiple columns. If our reference is just a single column (like D:D), we don’t need to specify a column number.

For example, =INDEX(B2:B6, 3) will return the value from the third cell in that range.

Putting it together

The real power comes from nesting MATCH inside INDEX. We’ll use our MATCH formula to dynamically figure out the row_num for our INDEX formula.

Let’s revisit our Product catalog sheet from before, but this time, let’s rearrange it to demonstrate the power of INDEX-MATCH. Notice that ProductID is no longer the first column.

If we tried to use VLOOKUP to find the Unit price using the ProductID, it wouldn’t work because ProductID is not the first column in our range.

This is a perfect scenario for INDEX() with MATCH(). We’ll use MATCH to find the row number for our ProductID in column C, and then use INDEX to pull the price from the same row in the column E.

Let’s say we have the ProductID 1003 in a cell, say D4, on our Sales data sheet:

• MATCH() part: We can find the row number for 1003 in the ProductID column, which is located on the Product catalog sheet. To do this, use =MATCH(D4, 'Product catalog'!C:C, 0). This formula will search in column C of the Product catalog sheet, find the value 1003, and return the number 15 (assuming it’s in the fifteenth row of the Product catalog sheet’s data).

• INDEX() part: We get the value from the Unit price column at the row number we just found. To do this, use =INDEX('Product catalog'!E:E, 15). This formula will get the value from the 15th row of the Unit price column, which is 75.

By combining these, we get our final, powerful formula: =INDEX('Product catalog'!E:E, MATCH(D4, 'Product catalog'!C:C, 0)).

This formula looks up the ProductID in cell D4 of the Sales data sheet, finds its position in column C of the Product catalog sheet, and then returns the corresponding value from column E.

While INDEX() with MATCH() is a powerful and classic solution, its two-part nature can be a bit complex to write and read. In the next section, we’ll look at the modern, all-in-one solution that Google Sheets has introduced to make this process even easier: XLOOKUP().

What is a XLOOKUP()?

Google Sheets has introduced XLOOKUP to combine all the power of the previous lookup functions into a single, easy-to-use function. Think of XLOOKUP as the “all-in-one” lookup tool; it can search in any direction, and it’s much simpler to write and read.

XLOOKUP is designed to be more intuitive, and it can do things that VLOOKUP and HLOOKUP can’t, like searching from right-to-left and easily handling errors.

The XLOOKUP() formula is structured with six parameters, some required and some optional, as mentioned below.

• search_key: This is the value we’re looking for.

• lookup_range: This is the single column or row where we’re searching for search_key. This is a major difference from VLOOKUP, where we had to select the entire table.

• result_range: This is the single column or row that contains the data we want to return. It’s the same size as our lookup_range.

• if_not_found (Optional): It’s the value to return if XLOOKUP can’t find a match. This lets us customize the error message instead of getting the standard #N/A.

• match_mode (Optional): This specifies the type of match to perform. It has four possible values, underlined below.

  • 0 (default): Finds an exact match. If no exact match is found, it returns the if_not_found value. This is the most common setting, and is used for precise lookups.

  • -1: Finds an exact match or the next smaller item if an exact match isn’t found. This is useful for things like finding a tax bracket, or a grade based on a score range.

  • 1: Finds an exact match or the next larger item if an exact match isn’t found.

  • 2: Finds a wildcard match. This allows us to use special characters like an asterisk * (to match any sequence of characters) or a question mark ? (to match any single character) in our search key.

• search_mode (Optional): This specifies the direction of the search. It has four possible values, underlined below.

  • 1 (default): Searches from the first item to the last. This is the standard search behavior.

  • -1: Searches in reverse order, from the last item to the first. This is useful if we need to find the most recent entry for a specific value.

  • 2: Performs a binary search on data that is sorted in an ascending order. This is a very fast search method for large datasets, but it requires the lookup range to be properly sorted.

  • -2: Performs a binary search on data that is sorted in a descending order. Like the previous option, this is a quick search that just requires the data to be sorted correctly.

Let’s understand XLOOKUP() with an example. Suppose we use our rearranged Product catalog sheet again to see how XLOOKUP() simplifies the formula.

To find the Unit price for ProductID 1003 (which is in cell D4 of a Sales data sheet) using XLOOKUP, we would do this:

1. search_key: The ProductID we are looking for, which is in cell D4 on our Sales data sheet.

2. lookup_range: The column where the ProductIDs are located, which is 'Product catalog'!C13:C16.

3. result_range: The column where the Unit price is, which is E13:E16.

Our complete formula is now much cleaner and easier to read: =XLOOKUP(D4, 'Product catalog'!C13:C16, 'Product catalog'!E13:E16).

This formula will search for the value in D4 within the range C13:C16 and, once it finds a match, it will return the corresponding value from the range E13:E16.

XLOOPUP vs VLOOPUP

Wrap up

In data analysis, moving from manual to automated tasks is a huge power-up. We began with VLOOKUP() and HLOOKUP(), which are great for simple lookups, then moved to the flexible INDEX() and MATCH() combination. This offered us the freedom to search in any direction. Finally, we saw how XLOOKUP() combines the strengths of other lookup functions into an easy, simple solution. By mastering these lookup tools, we’ve automated a core part of data analysis, that is retrieving and connecting data, saving ourselves from the task of carrying out tedious manual work, and setting the stage for more advanced, insightful analysis.