In your horizontal dataset, you may want to use Hlookup to return an entire column’s content. Is it possible? Yes! It is possible in Google Doc Spreadsheets. See How to return an entire column in Hlookup in Google Sheets.

Spreadsheet users are not in favor of the row-wise/horizontal data formatting. Normally column-wise/vertical dataset is easy to handle. Also, most of the functions in Google Sheets are well supported in using a vertical dataset.

But you may have your data horizontally arranged and that may be due to the data imported from some other source or the data is stored as part of form submission. In such datasets, if you want to look up a value, you can use Hlookup.

As I’ve stated above, the function Hlookup is capable of returning an entire column value in the output. See how to do that in the example formula below.

Example: How to Return an Entire Column in Hlookup in Google Sheets

Sample Data:

Note: The above data is sourced from this Wiki page using the following IMPORTHTML function.

=transpose(query(importhtml("https://en.wikipedia.org/wiki/List_of_waterfalls_by_height","table",2),"Select Col1, Col2, Col3, Col4 limit 10"))

First, understand the data. In this dataset, the first row contains the names of some of the waterfalls in the world.

In the second row, you can find its height, in the third row the locality of the waterfall and in the fifth row the country it resides.

I want to look up one ‘Waterfall’ by its name in the first row and return the details such as height, locality, and country.

The Formula that Returns an Entire Column in Hlookup in Google Sheets

Search_Key: “James Bruce Falls”

Formula:

=ArrayFormula(hlookup("James Bruce Falls",A1:J4,{2;3;4},false))

This formula searches across the first row and successfully finds the search_key in J1. It returns the values from rows 2, 3 and 4. The values are in row-wise. So use the semicolon instead of the comma as the separator.

The row indexes are inside the Curly Brackets and the formula would return an array result. So we should use the function ArrayFormula to wrap the Hlookup formula.

Hlookup with Entire Column Result (Formula Modification)

To return an entire column result in Hlookup, I mean infinite range, replace{2;3;4} (index) in the above formula withrow(A2:A).

=ArrayFormula(IFERROR(hlookup("James Bruce Falls",A1:J4,row(A2:A),false)))

Please note the following points.

1. You must use Iferror with Hlookup to remove #REF! error in such an open (infinite) range.
2. Also, use the formula outside the Hlookup range like in cell K1 or L1 as up to column J are occupied in the formula.

The ROW function returns the necessary multiple index numbers 2,3,4… when using within the ArrayFormula.

The same approach you can use in Vlookup but there replace the function ROW with the function COLUMN.

That’s all. Hope you could understand how to return an entire Column in Hlookup in Google Sheets. Enjoy!