How to Return an Entire Column in HLOOKUP in Google Sheets

In your horizontal dataset, you might want to use HLOOKUP to return an entire column’s contents. Is this possible? Yes! It’s possible in Google Sheets. See how to return an entire column in HLOOKUP in Google Sheets.

Introduction

Spreadsheet users typically prefer column-wise (vertical) data formatting because it’s easier to handle, and most Google Sheets functions work better with vertical datasets.

However, you may encounter data arranged horizontally—possibly imported from another source or collected through submissions in Google Forms. In such cases, if you need to look up a value, HLOOKUP (horizontal lookup) can be your go-to function.

As mentioned earlier, the HLOOKUP function can return an entire column from a horizontally arranged dataset. Let’s see how with an example.

Note: While LOOKUP and XLOOKUP can also perform similar tasks, LOOKUP requires sorted data, and XLOOKUP is feature-rich with more options.

Sample Data and the Problem

Here’s our sample data in A1:F4:

WaterfallAngel FallsTugela FallsNiagara FallsVictoria FallsIguazu Falls
Height (m)9799485110882
LocalityBolívarKwaZulu-NatalOntario/New YorkLivingstoneMisiones
CountryVenezuelaSouth AfricaCanada/USAZambia/ZimbabweArgentina/Brazil

Problem: In this dataset:

  • The first row contains the names of waterfalls.
  • Subsequent rows contain their respective Height, Locality, and Country.

We need to look up a waterfall by name (e.g., “Angel Falls”) and return all its details: Height, Locality, and Country.

Example: Returning an Entire Column with HLOOKUP

To retrieve the Country for “Angel Falls,” you would use the following formula:

=HLOOKUP("Angel Falls", A1:F4, 4, FALSE)

This formula looks for “Angel Falls” in the first row (A1:F1), and returns the value from the 4th row of the corresponding column (B4), which is “Venezuela”.

But what if you want to return the entire column (B1:B4)? Here’s how you can do it:

=ArrayFormula(HLOOKUP("Angel Falls", A1:F4, SEQUENCE(ROWS(A1:F4)), FALSE))
Example of Returning an Entire Column Using HLOOKUP in Google Sheets

You can also use an open range (e.g., A1:F) in the formula for dynamic datasets.

Explanation:

  1. ARRAYFORMULA: Ensures the formula processes multiple values at once.
  2. SEQUENCE(ROWS(A1:F4)): Generates a sequence of index numbers from 1 to the number of rows in the range (1, 2, 3, 4).
  3. HLOOKUP: Uses these sequence numbers to return values from all rows of the column where “Angel Falls” is found.

Return an Entire Column in HLOOKUP While Excluding the Header Row

By default, the HLOOKUP function searches for the key in the first row of the range. If you want to exclude this header row from the results, modify the SEQUENCE function to start from 2 instead of 1:

SEQUENCE(ROWS(A1:F4)-1, 1, 2)

This adjustment ensures the sequence starts at 2 and generates index numbers for rows below the header.

Updated formula:

=ArrayFormula(HLOOKUP("Angel Falls", A1:F4, SEQUENCE(ROWS(A1:F4)-1, 1, 2), FALSE))

How It Works:

  • ROWS(A1:F4)-1: Reduces the total row count by 1 to exclude the header row.
  • SEQUENCE(…, 1, 2): Starts the sequence from 2, targeting rows below the header.

Resources

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.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

1 COMMENT

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.