VLOOKUP vs HLOOKUP

Article byWallstreetmojo Team
Edited bySheeba M
Reviewed byDheeraj Vaidya, CFA, FRM

Difference Between VLOOKUP Vs HLOOKUP

VLOOKUP or Vertical-LOOKUP and HLOOKUP or Horizontal-LOOKUP are referencing functions in Excel that reference data to match a table array or a group of data and display the output. The difference between these referencing functions is that VLOOKUP references with columns while HLOOKUP references with rows.

For example, when we apply the VLOOKUP and the HLOOKUP formulas on the dataset we will have the same output displayed column-wise and row-wise, respectively. Let us consider the same in the article below.

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VLOOKUP vs HLOOKUP (wallstreetmojo.com)

Key Takeaways

  • The VLOOKUP vs HLOOKUP helps users retrieve the required data using the lookup_value.
  • VLOOKUP or Vertical LOOKUP is used to retrieve column-wise data.
  • HLOOKUP or Horizontal LOOKUP is used to retrieve row-wise data.
  • Both functions have the same arguments. One difference is the col_index_num for VLOOKUP, and the row_index_num for HLOOKUP.
  • If we do not provide the range_lookup valueas 0 or 1, as it is an optional value, Excel takes 1 or TRUE as the default input to return the approximate match.

What Is VLOOKUP?

The VLOOKUPVLOOKUPThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more function is used when we have a set of vertical data. It allows us to search a range of data references with columns, and retrieves the right information we are looking for.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

VLOOKUP Formula And Example

The syntax of the VLOOKUP formula is,

Vlookup Formula

The arguments of the VLOOKUP formula are,

  • lookup_valueThe value for which we are trying to retrieve the result from the table_array (2nd argument)It is a mandatory argument.
  • table_arrayIt will be either range or table_array where we search for the lookup_value. It is a mandatory argument.
  • col_index_num: In a given table_array, it isthe column we are looking for the result. It is a mandatory argument.
  • [range_lookup]: In this optional argument, we need to specify the kind of match we need:
    • 0 or FALSE – It will search for the exact match of the lookup_value in the table_array. If nothing is specified, 1 or TRUE will be the default mode.
    • 1 or TRUE – It will search for the approximate match of the lookup_value in the table_array.

Let us take an example to illustrate VLOOKUP. In the below image, we have a table with the “Employee ID”, “Employee Name”, and “Performance Grade by HR”.

Example Given Data

With just the employee ID, we will retrieve the employee’s name and the performance grade given by HR using the VLOOKUP formula.

The output using the VLOOKUP formula is,

Function Data

Output Interpretation: VLOOKUP works when the table is vertically set up, i.e., a column-wise table.

What Is HLOOKUP?

The HLOOKUPHLOOKUPHlookup is a referencing worksheet function that finds and matches the value from a row rather than a column using a reference. Hlookup stands for horizontal lookup, in which we search for data in rows horizontally.read more function is used when we have a set of horizontal data. It allows us to search a range of data references with rows, and retrieves the right information we are looking for.

HLOOKUP Formula And Example

Hlookup Formula

The arguments of the HLOOKUP formula are,

  • lookup_valueThe value for which we are trying to retrieve the result from the table_array (2nd argument)It is a mandatory argument.
  • table_arrayIt will be either range or table_array where we search for the lookup_value. It is a mandatory argument.
  • row_index_num: In a given table_array, it isthe row we are looking for the result. It is a mandatory argument.
  • [range_lookup]: In this optional argument, we need to specify the kind of match we need:
    • 0 or FALSE – It will search for the exact match of the lookup_value in the table_array. If nothing is specified, 1 or TRUE will be the default mode.
    • 1 or TRUE – It will search for the approximate match of the lookup_value in the table_array.

Let us take an example to illustrate HLOOKUP. In the below image, we have a table with the “Employee ID”, “Employee Name”, and “Performance Grade by HR”.

Example Given Data

With just the employee ID, we will retrieve the employee’s name and the performance grade given by HR using the HLOOKUP formula.

The output using the HLOOKUP formula is,

Employee Information

Output Interpretation: 

  • HLOOKUP works when the table setup is horizontal, i.e., a row-wise table.

Therefore, the functions VLOOKUP and HLOOKUP retrieved the same result, but the table is arranged differently.

VLOOKUP vs HLOOKUP Infographics

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VLOOKUP vs HLOOKUP (wallstreetmojo.com)

VLOOKUP vs HLOOKUP – Key Differences

The key differences are as follows:

  • While applying the VLOOKUP function, we need a vertical table, and for the HLOOKUP function, we need a horizontal table.
  • VLOOKUP is a more popular function in Microsoft Excel, and many use it. At the same time, HLOOKUP is not used frequently and is only used for some scenarios.
  • Both are used to get the same output but with different look, i.e., row-wise and column-wise.
  • The formula of VLOOKUP=VLOOKUP (lookup_value, table_array, col_index_number, [range_lookup]), and the formula of HLOOKUP is =HLOOKUP (lookup_value, table_array, row_index_number, [range_lookup]). There is only one difference between these formulas: row and column.
  • VLOOKUP helps you find out the data in the left-most column. On the other hand, HLOOKUP is used to find the data from a range in the bottom-most rows.

VLOOKUP vs HLOOKUP Comparative Table

Basic for ComparisonVLOOKUPHLOOKUP
MeaningIt is used to find particular data from a vertical spreadsheet.It is used to find particular data from a horizontal spreadsheet.
UsageIt is one of the most frequently used functions in Excel.It is used but not as frequently as VLOOKUP.
OutputThe output is column-wise.The output is row-wise.
Type of tableVertical table.Horizontal table.
Searched dataWe search for the data in the left-most column.We search for the data in the bottom-most row.
Syntax=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])=HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])

Recommended Articles

This article is a guide to VLOOKUP vs HLOOKUP. Here we retrieve data using vertical column-wise or horizontal row-wise data, examples & downloadable templates. You may also have a look at the following articles: –

Reader Interactions

Comments

  1. Musty says

    Good job!!!

    • Dheeraj Vaidya says

      Thanks for your kind words!

Leave a Reply

Your email address will not be published. Required fields are marked *