Google Sheets Tutorial: Using VLOOKUP and HLOOKUP effectively

In this tutorial, you will learn everything you need to know about the reference functions in Google Sheets, especially the VLOOKUP and HLOOKUP. These functions are crucial for working efficiently in spreadsheet programs, especially when the data is not in the ideal structure. With VLOOKUP, you can retrieve data from one list into another and match the corresponding values. Additionally, we will show you how HLOOKUP works when the data is arranged horizontally. Let's get started right away.

Main Insights

• VLOOKUP searches for a key in the first column of a specified range and returns the desired value from a specified row.
• HLOOKUP functions similarly to VLOOKUP but with horizontal data.
• Fixed cell references are important to ensure that the correct ranges are used in formulas.

Step-by-Step Guide

Using VLOOKUP

To effectively utilize VLOOKUP in Google Sheets, follow these steps:

Firstly, ensure that your data is present in two sheets. One sheet contains the sales figures with reference numbers, and another sheet contains the prices for those numbers. This structure is fundamental for correctly applying VLOOKUP.

Furthermore, it is crucial to use the correct column as the key. In our example, we will link the sales figures with the prices by using the item reference number as the key.

Next, insert a new column to calculate the prices. Select the cell where you want to insert the price and use the formula for VLOOKUP. The basic syntax is: =VLOOKUP(search_key, range, index, [is_sorted]).

In our case, we could specify the reference number as the search key and the range containing the prices.

Then, set the index to the column containing the price and make sure to set the last parameter (is_sorted) to "false" for an exact match.

Once you have entered the formula, ensure that the cell references are correct. This means that the range containing the prices needs to be fixed. You can do this by using the F4 key, which will prevent the range from being dragged down when copying the formula down.

Drag the formula down to get the prices for all items. This ensures that the revenue for each item can be calculated simply by multiplying the quantity by the price.

Introducing HLOOKUP

If your data is structured horizontally and you want to work with HLOOKUP, follow these steps:

Create a new sheet where your corresponding values are arranged in a table. Ensure that the departments or categories are in the first row.

You can use HLOOKUP in a similar way to VLOOKUP. Start by entering the formula =HLOOKUP(search_key, range, index, [is_sorted]). The search key, as before, is the department or category for which you want to query the salary.

Ensure that the index is the row containing the salary. Define the last parameter as "false" here as well to get exact matches.

Again, you should fix the cell references by using the F4 key. This is particularly important so that your range is not altered when dragging the formula down. Drag the formula downwards afterwards.

Summary

In this tutorial, you have learned how to use the reference functions VLOOKUP and HLOOKUP in Google Sheets. You now know how to use VLOOKUP for vertical data structures and how HLOOKUP is applied for horizontal data. Fixed cell references are helpful to ensure that your formulas remain reliable. These skills will help you efficiently link and evaluate data.