Top 5 Differences Between VLOOKUP & HLOOKUP in Microsoft Excel

Microsoft Excel is reckoned as one of the best and user-friendly spreadsheet software. Because of this feature this spreadsheet software is widely used around the world. According to the news inputs the latest version Office 365 comes with additional features like cloud services, making it a more powerful spreadsheet software. Microsoft Excel comes with 400 features, which are very easy to use and understand, making data analysis manipulations very easy.

Microsoft Excel offers a Lookup function, that helps you in searching values in a column or row. The letter V in VLookup stands for vertical [column] on the other hand HLookup refers to Horizontal [row]. The knowledge and hands-on experience in using VLookup and HLookup functions are mandatory for some jobs. Initially using these functions may be complicated for the novice users but once they get a clear understanding of this functionality, they will have a better idea where to use these functions to get the optimum results.

Difference 1: 

The VLookup function is one of the important functions in Microsoft Excel. This function helps you to locate specific information in your spreadsheet. When the user uses VLookup function for finding specific information in MS Excel spreadsheet, each matching information is displayed in the same row but in the next column.  

Microsoft Excel defines HLookup as a function that “looks for a value in the top row of a table or array of values and returns the value in the same column from a row you specify”. The HLookup function retrieves data from the table horizontally. According to some experts most of the data in MS Excel sheet is created with vertical tables, the HLookup function is used less in comparison to VLookup function

Difference 2:

VLookup Syntax

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

In the above VLookup syntax, lookup_value refers to the value that the user is trying to find in a table, and table_array refers to the range or table in which the user is looking for a certain value to match another value. 

HLookup Syntax:

HLOOKUP( lookup_value, table_array, row_index_num, [range_lookup] )

In the above syntax lookup_value refers to the value that the user wants to look for, table_array refers to the data or table the user is searching for, and row_index_num in this refers to the row number array, that the user wants the corresponding value to be returned from

The [range_lookup]  is an optional logical argument, which can be set to TRUE or FALSE by the user. If it is set to TRUE and if there is no exact match found, this will return the closest match to the given value. If it is set to FALSE by the user, and if the given value is not found in the spreadsheet then it will return an error message. The user can also make use of wild (“? and *”)characters in their Hlookup function , where" ? "is used to match any single character and (*) is used to match a sequence of characters.

Difference 3:

One of the pre-requisite for using VLookup function is that the value the user is searching must be located in the left-most column of the table/range.

In HLookup syntax, the pre-requisite is that the value the user is searching in the table or range must be in the top-most column. 

Difference 4:

Though MS Excel HLookup function is a very useful function for creating horizontal Lookups but is not very popular in comparison to VLookup function because, most of the tables are are vertical MS Excel, and that is the reason this HLookup function is not that popular in comparison to VLookup function.

Difference 5:

HLookup searches for a value in the top row of a table and then returns a value in the same column. The VLookup function displays the searched value in the same row but in the next column. 

Conclusion

The MS Excel spreadsheet software features are very user-friendly, the user can easily use these functions to manipulate and analyze their data in the spreadsheet. VLookup and HLookup functions are two functions that are used for finding specific data in the excel sheet. This function is very useful as it easily locates the data or the value. If we compare both VLookup and HLookup functions, we find that VLookup function is more frequently used by the MS Excel users in comparison to HLookup function.

 

Click Here for MS Excel Course

Date
About Author
Suha Emma
Category