Lookup, Vloookup, and Hlookup are the three very useful lookup functions. You must know the Lookup, Vlookup, and Hlookup differences in Google Sheets so that you can use them correctly.
Once you complete this tutorial, go to my Google Sheets functions guide where you can see the links to the above functions under the title LOOKUP.
The Purpose of Lookup, Vlookup, and Hlookup in Google Sheets
The purpose of the above three lookup functions is the same, i.e., in a dataset (range), you can search for a keyword (search_key) and find related information from other cells.
For example, I have a list with fruit names. I can search for the fruit name “Apple” in that list and return its price, qty. or any other information that is present.
In the available three lookup functions, Lookup, Vlookup, and Hlookup, which one to use?
To decide that you should first know the Lookup, Vlookup, and Hlookup differences in Google Sheets.
No doubt Vlookup is the best lookup function in Google Sheets and most common in use. But at certain occasions Lookup outscores Vlookup.
To understand this first see the differences between Lookup, Vlookup, and Hlookup in Google Sheets.
Lookup, Vlookup, and Hlookup Differences in Google Sheets
The key in any lookup related formula is the data orientation. First, you should check whether your data is arranged horizontally or vertically.
Understand Vertical and Horizontal Dataset in Google Sheets
Just check the above data and you can see that both of the data are the same. But the first dataset is arranged in horizontally and the second dataset in vertically.
Lookup Function Differences
Based on the dataset shown above, I am trying to point out the differences between Lookup, Vlookup, and Hlookup in Google Sheets.
Vlookup
1. The function Vlookup is for vertical lookup. So it supports only vertical dataset (please refer the above image).
In Vlookup, the search key should be from the first column as the formula only searches the search key in the first column. But advanced users can overcome this by rearranging the columns within the formula.
Syntax:
VLOOKUP(search_key, range, index, [is_sorted])
Example Formula:
=vlookup("Angel Falls",A7:D12,4,False)
This formula searches down the first column for “Angel Falls” and return the Country name from the 4th column.
Hlookup
2. The function Hlookup is for horizontal lookup. So it supports only horizontal dataset (please refer the above image).
In Hlookup, the search key should be from the first row as the formula only searches across the first row for the search key. Here also advanced Hlookup users can set an exception to this by rearranging the rows within the formula.
Syntax:
HLOOKUP(search_key, range, index, [is_sorted])
Example Formula:
=Hlookup("Angel Falls",A1:F4,4,False)
This formula searches across the first row for “Angel Falls” and return the Country name from the 4th row.
3. Both Vlookup and Hlookup can use in sorted as well as unsorted data. The FALSE in the formula represents unsorted data and exact match. If it’s TRUE, it means you are using the function in a sorted data and the formula can go for the nearest match.
Lookup
4. The function Lookup is more flexible. That’s why this function doesn’t contain any V or H prefix in front of its name. You can use Lookup in vertical as well as horizontal datasets.
But there is a shortfall. You can only use Lookup function in a sorted dataset. Of course here also advanced users can find some workaround. I am not going to that as it can make you totally confused.
Important: The function Lookup can itself identify whether the range is a horizontal dataset or a vertical dataset. Yes! Here Lookup scores.
The usage of Lookup is slightly different from Vlookup and Hloookup. For the example purpose, I am sorting the above dataset. Then only we can test this function. See that below.
Please note that in the above dataset, the first row in the first table is sorted and the first column in the second table is sorted.
You can use Lookup in two different ways. For my explanation purpose, I am calling it Lookup Type 1 and Lookup Type 2.
Lookup Type 1 (Return Value from Last Row / Last Column)
Syntax 1:
LOOKUP(search_key, search_result_array)
I am again repeating. You can use the same Lookup formula for both the horizontal as well as the vertical dataset.
If you want to return the value from the last row in the horizontal dataset, use the formula as below.
=lookup("Angel Falls",A1:F4)
If you want the value from the last column from the vertical dataset, the formula would be as follows.
=lookup("Angel Falls",A7:D12)
The first formula searches across the first row for the key “Angel Falls” and return the country name from the last row.
The second formula searches down the first column for the key “Angel Falls” and return the country name from the last column.
In the above two formulas, I haven’t made any changes w.r.t to the data orientation. The formula is same for horizontal as well as vertical range.
I’ve only changed the data range in the formula. The Lookup function automatically adjusts to the data orientation. How?
If there are more columns than rows in the data range, it’s considered as a horizontal dataset/range.
An equal number of rows and columns or more rows than columns is considered as the vertical dataset/range.
Lookup Type 2 (Return Value from Any Row / Any Column)
Syntax 2:
This time Lookup can return the result from any row (horizontal lookup) or column (vertical lookup)
LOOKUP(search_key, search_range, result_range)
Again Lookup works well on both horizontal as well as on the vertical dataset.
Horizontal Data:
=lookup("Angel Falls",A1:F1,A4:H4)
Vertical Data:
=lookup("Angel Falls",A7:A12,D7:D12)
These are the Lookup, Vlookup, and Hlookup differences in Google Sheets. Now see some tips and tricks.
Vlookup and Hlookup Tips
You can use Vlookup in Horizontal dataset too. Yes! you heard me right. How? Use the TRANSPOSE function in the range as below.
Horizontal Data in Vlookup.
=vlookup("Angel Falls",transpose(A1:F4),4,False)
It’s applicable to Hlookup too. There are more tips and tricks included in my Functions Guide. Also, you can search this site with the keyword Vlookup/Hlookup for more formula examples.
That’s all about Lookup, Vlookup, and Hlookup Differences in Google Sheets. Enjoy!
