VLOOKUP Vs HLOOKUP In Google Sheets

Difference Between VLOOKUP Vs HLOOKUP In Google Sheets

The difference between VLOOKUP vs HLOOKUP in Google Sheets is that the VLOOKUP() looks for a specific value in the leftmost column of the search range and returns the corresponding value from the specified column on the right. On the flip side, the HLOOKUP() looks for a particular value in the first row of the search range and returns the corresponding value from the cited row below.

Users can utilize the VLOOKUP vs HLOOKUP in Google Sheets to determine the function that best fits their needs. While the VLOOKUP() helps perform a lookup of a value in a vertical table, the HLOOKUP() performs a lookup of a value in a horizontal table.

For example, we have a list of employees and their monthly salaries in a vertical dataset (cell range A1:B6) and a horizontal dataset (cell range A8:F9).

The requirement is to update the monthly salary of the employee cited in cell I2 based on the two datasets and display the output in the third dataset.

Then, we can use the VLOOKUP() and HLOOKUP(), that work similar to Excel VLOOKUP function and HLOOKUP function, to update the monthly salary of the specified employee based on the vertical and horizontal datasets, respectively. We shall use cells I3:I4 to display the output.

In this VLOOKUP vs HLOOKUP in Google Sheets example, we apply the VLOOKUP() and HLOOKUP() in the target cells I3:I4.

Both functions accept four arguments. The first argument is the search value, which is the reference to cell I2 holding the cited employee name, Mindy Frank. The second argument is the search range, which is a vertical lookup range A2:B6 in the case of VLOOKUP() and a horizontal lookup range B8:F9 in the case of HLOOKUP(). So, the VLOOKUP() searches for the employee name in the first column of the search range, and the HLOOKUP() looks for the employee name in the first row of the search range. Next, the third argument is 2. It indicates that the functions must return the corresponding value from the second column and row in the two datasets containing the monthly salary data, respectively. Finally, the last argument value is 0, indicating that the two functions must find the exact match for the search value.

Thus, based on the inputs, the VLOOKUP() performs a vertical lookup and HLOOKUP() conducts a horizontal lookup. They then return the required monthly salary figure of the specified employee, $4,700, from the respective search ranges in the target cells I3:I4.

Let us understand the VLOOKUP() and HLOOKUP() individually to learn about VLOOKUP vs HLOOKUP in Google Sheets more effectively.

What Is VLOOKUP In Google Sheets?

The VLOOKUP in Google Sheets is an inbuilt Lookup function that stands for Vertical Lookup. The function helps determine data in columns, with the search performed in the search range vertically. The function requires us to specify the index of the column from where the function must fetch the return value.

VLOOKUP Formula And An Example

The VLOOKUP formula is the following:

Where,

• search_key: The value to look for in the first column from the left in the search range.
• range: The search range, where the leftmost column is scrutinized for the search_key.
• index: The index of the column in the search range from where the function must return the value corresponding to the search_key. Please note that the index should be a positive integer.
• is_sorted: The optional argument value can be logical TRUE (1) or FALSE (0).
  • The logical value of FALSE indicates that the function must find an exact match for the search_key, and it is the recommended argument value.
  • The logical value of TRUE indicates that the function should find an approximate match for the search_key. The function considers it as the default value of the argument, when ignored.

Please note that before we use the approximate match option, we must sort our search column in the search range in ascending order. Otherwise, we may get an incorrect output.

Example

The source dataset shows the monthly sales at various branch offices of a firm.

The requirement is to update the April month sales data in cells J2:J3 and J6:J7 for the branch offices cited in cells I2:I3 and I6:I7.

Step 1: Choose cell J2, enter the VLOOKUP(), and press Enter.

=VLOOKUP(I2,$A$3:$E$8,5,0)

The first argument is the reference to the cell I2 holding the search_key, Oakland. Next, the search rangeis $A$3:$E$8, which is the second argument range. After that, the function should return the monthly sales value from the fifth column, with column A counted as the first column in the search range. So, the third argument is 5. Finally, the cited search_key is a value present in the source dataset. So, the function should conduct an exact match for the search_key to locate the corresponding return value. Thus, the last argument, is_sorted, is 0.

Thus, the function searches the search key value of Oakland in the first column of the search range A3:E8. It finds an exact match in cell A7. Then, the function returns the value in the same row but in the fifth column of the search range, which is the cell E7 value of $92,000.

Next, using the fill handle, which works like the Excel fill handle, implement the formula in cell J3.

Step 2: Choose cell J6, enter the VLOOKUP(), and press Enter.

=VLOOKUP(I6,$A$3:$E$8,5,1)

The first argument is the reference to the cell I6 holding the search_key, Orlando. After that, the search rangeis $A$3:$E$8, which is the second argument range. Next, the function must return the monthly sales figure from the fifth column, with column A counted as the first column in the search range. So, the third argument is 5. Finally, the given search_key is a value not present in the source dataset. So, the function should carry out an approximate match for the search_key to achieve the corresponding return value. Thus, the last argument, is_sorted, is 1.

Thus, the function looks for the search key value of Orlando in the first column of the search range A3:E8. It locates an approximate match in cell A7. The reason is that the value of Oakland in cell A7 is the nearest match to the search_key. Then, the function returns the value in the same row but in the fifth column of the search range, which is the cell E7 value of $92,000.

Next, using the fill handle, feed in the formula in cell J7.

What Is HLOOKUP In Google Sheets?

The HLOOKUP in Google Sheets is an inbuilt Lookup function, and it stands for Horizontal Lookup. It helps determine data in rows, with the search conducted in the search range horizontally. The function requires us to specify the index of the row from where the function must get the required return value.

HLOOKUP Formula And An Example

The HLOOKUP formula is the following:

Where,

• search_key: The value we want to look for.
• range: The search range, where the top row is checked for the search_key.
• index: The index of the row from where the function must get the return value, with the top row in the range counted as the number one row. Please note that if the index argument value is not between 1 and the row count in the range, the function output will be the #VALUE! error.
•  is_sorted: The argument value points out if the values in the top row of the specified   

 range the function must check are sorted.

• The default value of the argument is TRUE (or 1). So, when we set the value as TRUE or ignore it, the function returns the nearest match, equal to or below the search_key. However, when all the values in the search row are above the search_key, the function output is the #N/A error.
  • Consider that the supplied argument value is TRUE or ignored. However, the values in the top row of the cited range are not sorted. In such a case, the HLOOKUP() mightreturn an incorrect output.
  • Assume the supplied argument value is FALSE (or 0). Then, the function looks for an exact match for the search_key to determine the required return value. However, if there are multiple matches, the function output is the cell data corresponding to the first match. On the flip side, if the function cannot find any matching value, it returns the #N/A error.

While the first three arguments are compulsory, the last argument value is optional.

Example

The source dataset is horizontal. It shows the date-wise inventory level at a store.

The aim is to update the inventory levels in cells B6 and B9 for the dates cited in cells B5 and B8.

Step 1: Choose cell B6, enter the HLOOKUP(), and press Enter.

=HLOOKUP(B5,$B$1:$F$2,2,0)

The first argument is the reference to the cell B5 holding the search_key, 15-July-2024. Next, the search rangeis $B$1:$F$2, which is the second argument range. Next, the function must return the inventory level data from the second row, considering row 1 as the first row in the search range. So, the third argument is 2. Finally, the specified search_key is a date present in the source dataset. So, the function must perform an exact match for the search_key to determine the corresponding return value. Thus, the last argument, is_sorted, is 0.

So, the function searches the search key value of 15-July-2024 in the first row of the search range B1:F2. It finds an exact match in cell E1. Then, the function returns the value in the same column but in the second row of the search range, which is the cell E2 value of 3500.

Step 2: Choose cell B9, enter the HLOOKUP(), and press Enter.

=HLOOKUP(B8,$B$1:$F$2,2,1)

The first argument is the reference to cell B8 holding the search_key, 20-July-2024. Next, the search rangeis $B$1:$F$2, which is the second argument range. Next, the function should return the inventory level value from the second row, with row 1 considered as the first row in the search range. So, the third argument is 2. Finally, the specified search_key is a date not present in the source dataset. So, the function must perform an approximate match for the search_key to find the corresponding return value. Thus, the last argument, is_sorted, is 1.

So, the function searches the search key value of 20-July-2024 in the first row of the search range B1:F2. It finds an approximate match in cell E1. The reason is that the date 15-July-2024 in cell E1 is the closest match to the search_key since it is below it. Then, the function returns the value in the same column but in the second row of the search range, which is the cell E2 value of 3500.

How To Vlookup And Hlookup Together In Google Sheets?

Though we can do a comparison of VLOOKUP vs HLOOKUP in Google Sheets, we can use the two functions together to yield practical results. For example, the formula is useful when we want to fetch data from specific columns and rows.

The steps to use them together are as follows:

Step 1: Select a cell to showcase the output.

Step 2: Enter the following formula containing the VLOOKUP and HLOOKUP functions.

=VLOOKUP(search_key,range,HLOOKUP(search_key,range,index,0),0)

We supply the HLOOKUP() as the index argument to the VLOOKUP(), with all the arguments having the same interpretation as explained earlier.

Step 3: Press Enter to view the formula output.

Example

The source dataset contains a set of stationery items and their yearly units sold data from 2018-23. Also, row 3 shows the column numbers containing the yearly units sold data, with the numbering starting from 2.

The task is to update the units sold data for the cited stationery item in cell A12 and the year specified in cell B12. Assume the target cell is C12.

Step 1: Select cell C12, enter the formula containing the VLOOKUP and HLOOKUP functions, and press Enter.

=VLOOKUP(A12,A4:G8,HLOOKUP(B12,B2:G3,2,0),0)

First, the HLOOKUP() searches for an exact match of the search_key value, 2021, in the range B2:G3. It finds it in cell E2. So, the function returns the value in the same column but the next row, which is the cell E3 value of 5.

Next, the VLOOKUP() searches for an exact match of the search_key value, Folder, in the range A4:G8. It finds it in cell A5. Next, the function considers the HLOOKUP() output value of 5 as the index of the column from where the VLOOKUP() must get the return value. Thus, the function returns the value in the same row but the fifth column in the search range, which is the cell E5 value of 24000.

Now let us see the key differences and a comparative table for VLOOKUP vs HLOOKUP in Google Sheets.

VLOOKUP Vs HLOOKUP – Key Differences

The key differences when we check VLOOKUP vs HLOOKUP in Google Sheets are as follows:

• Data Arrangement

In the case of the VLOOKUP(), the data in the search range is arranged column-wise, from up to down.

On the flip side, for the HLOOKUP(), the data in the search range is arranged row-wise, from left to right.

• Search_key Search Direction

The VLOOKUP function searches for the search_key vertically, going from up to down the search range.

In contrast, the HLOOKUP() looks for the search_key horizontally, moving from left to right across the search range.

• Search Value Location

In the case of the VLOOKUP(), the search value should be in the leftmost column of the search range. On the other hand, for the HLOOKUP(), the search value should be in the first row of the search range.

• Function Return Value Location

The VLOOKUP() determines the required return value from the same row but in the specified column in the search range. In contrast, the HLOOKUP() locates the required return value from the same column but in the specified row in the search range.

VLOOKUP Vs HLOOKUP Comparative Table

The comparative table for VLOOKUP vs HLOOKUP in Google Sheets is as follows:

Factor	VLOOKUP	HLOOKUP
Full Form	Vertical Lookup	Horizontal Lookup
Purpose	Looks for a value in a vertical dataset.	Looks for a value in a horizontal dataset.
Syntax	VLOOKUP(search_key,range,index,[is_sorted])	HLOOKUP(search_key,range,index,[is_sorted])
Index	Indicates the column number from where the function must fetch the return value.	Indicates the row number from where the function must fetch the return value.
Lookup Direction	Vertical	Horizontal
Lookup Value	Present in the leftmost column of the search range.	Present in the first row of the search range.

Download Template

This article must be helpful to understand VLOOKUP vs HLOOKUP In Google Sheets, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to to Difference Between VLOOKUP & HLOOKUP In Google Sheets. We discuss their formula and their differences using a comparative table. You can learn more from the following articles. –

INDEX MATCH Multiple Criteria In Google Sheets

XNPV in Google Sheets

DDB in Google Sheets