VLOOKUP and HLOOKUP Unearthed – Google Docs Spreadsheet

Published on

This is an introductory post on the VLOOKUP and HLOOKUP functions in Google Sheets, featuring basic examples and links to detailed tutorials on this blog.

The essence of this post is to help you comprehend the significance of vertical and horizontal lookups in Google Sheets.

For spreadsheet users engaged in serious data entry, knowing how to use VLOOKUP and HLOOKUP is a source of pride. Recently, XLOOKUP has gained prominence in this context.

I’ve emphasized VLOOKUP in this post. Begin by understanding how to use VLOOKUP in Google Docs Spreadsheet, and then proceed similarly with HLOOKUP. As always, I’ve aimed to keep this tutorial as simple as possible.

What Is VLOOKUP?

If your data is organized in rows, you can employ the VLOOKUP function to search for a specific value.

The function utilizes a search key, scanning down the first column of the lookup range (table) and retrieving a value from the specified column or columns.

It identifies the initial instance of the search key in the first column of the range and delivers the corresponding result from the same row.

When employing numbers, dates, datetime, or time as the search key, if no exact match is found, the formula will return the value less than or equal to the search key—provided the data is sorted A-Z.

What Is HLOOKUP?

If your data is organized in columns, you can utilize the HLOOKUP function to search for a specific value.

Unlike VLOOKUP, HLOOKUP searches for the key in the first row and returns the value from the corresponding columns. All other features remain the same.

VLOOKUP Use Case: Basic Example to Understand the Function

Syntax:

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

Arguments:

  • search_key: The value to search.
  • range: The range to search.
  • index: The column index, e.g., 5.
  • is_sorted: Choose TRUE (sorted) or FALSE (unsorted).

Before we start with the formula, type the following data in a Google Docs Spreadsheet in the range A1:D6.

Item CodeDescriptionUnit Price (Ton)Markup
W-220White Pebbles 20 mm750.0022%
Bl-220Black Pebbles 20 mm800.0024%
W-240White Pebbles 40 mm700.0026%
Bl-240Black Pebbles 40 mm750.0022%
Br-240Brown Pebbles 40 mm800.0030%

Problem:

Find the retail price of the item “White Pebbles 40 mm” by searching for the item code “W-240” in the range.

To find the retail price, we need to first Lookup the unit price and markup percentage of “W-240” and apply the following formula:

Retail Price=Unit Rate×(1+Markup Percentage)

We will use one VLOOKUP to extract the unit rate and another VLOOKUP to extract the markup percentage.

The following VLOOKUP will return the unit rate from the table:

=VLOOKUP("W-240", A1:D6, 3, false)

Where:

  • search_key: “W-240”
  • range: A1:D6
  • index: 3 (to return the result from the third column)
  • is_sorted: false

To return the markup percentage, we can use the same formula but with index # 4, and here it is:

=VLOOKUP("W-240", A1:D6, 4, false)

Now let’s apply the formula to calculate the retail price, and here it is:

=VLOOKUP("W-240", A1:D6, 3, false)*(1+VLOOKUP("W-240", A1:D6, 4, false))
VLOOKUP Unearthed - Google Docs Spreadsheet

I hope the above example helps you understand when and where to use the VLOOKUP function in Google Sheets. For detailed information on the function, please refer to the following guides.

Mastering VLOOKUP: In-Depth Guides and Tips for Success

Core Function:

Supporting Tutorials:

Improve the Level:

HLOOKUP Use Case: Basic Example to Understand the Function

Syntax:

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

Arguments:

  • search_key: The value to search.
  • range: The range to search.
  • index: The row index, e.g., 3.
  • is_sorted: Choose TRUE (sorted) or FALSE (unsorted).

Now, you can grasp the utilization of HLOOKUP through the following example and subsequent links to detailed tutorials.

First, copy and paste the above data as follows into the range A14:F17 using the Transpose function in your Google Sheets.

HLOOKUP Unearthed - Google Docs Spreadsheet

Now that the data is arranged in columns, let’s address the same problem that we solved using VLOOKUP here.

We are using HLOOKUP because the data is transposed, and we want to search the first row and return values from the third and fourth rows in the found column.

The following HLOOKUP will return the unit rate from the table:

=HLOOKUP("W-240", A14:F17, 3, false)

Where:

  • search_key: “W-240”
  • range: A14:F17
  • index: 3 (to return the result from the third row)
  • is_sorted: false

To return the markup percentage, we can use the same formula but with index # 4, and here it is:

=HLOOKUP("W-240", A14:F17, 4, false)

To get the retail price, let’s apply the formula: Retail Price=Unit Rate×(1+Markup Percentage)

=HLOOKUP("W-240", A14:F17, 3, false)*(1+HLOOKUP("W-240", A14:F17, 4, false))

Mastering HLOOKUP: In-Depth Guides and Tips for Success

Core Function:

Supporting Tutorials:

Improve the Level:

Conclusion

The purpose of this tutorial is to help you comprehend the VLOOKUP and HLOOKUP functions. Both of these functions require detailed study, which cannot be fully covered in a single tutorial.

To further your understanding, I have included links to several advanced lookup tutorials in this post. Additionally, you can discover more by using the search feature within this blog.

Thank you for reading.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Previous Values Dynamically in Excel and Google Sheets

Sometimes, you may want to look up the previous values relative to the current...

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

5 COMMENTS

  1. Hi,

    Is it possible to import every Nth Cell from another Sheet?

    I am trying to import every 6th cell from Column C. I’ve tried doing this but I keep ending up with the imported data appearing every 6 rows.

    I then tried to sort the range this compiled the data but also alphabetized it which I don’t want. Thanks!

  2. I’m trying to use Vlookup to pull a value from a different tab by referencing that tab from the matching string in the cell above it (attempting to use the Indirect function).

    My formula looks like this:

    =VLOOKUP($B5,'(=INDIRECT("C3"))'!$O$3:$S$6,5,false)

    Where C3 contains a text string which has the same name as the TAB I’m trying to pull the data from. It works fine when I simply write the name of the tab in the formula ie =VLOOKUP($B5,'Sheet1'!$O$3:$S$6,5,false) but this is tedious for copying the formula across cells, because each cell is pulling data from a separate tab (with different sheet name of course). Hope that is clear.

    Suggestions?

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.