VLOOKUP vs HLOOKUP

Publication Date :

Blog Author :

Edited by :

Table Of Contents

arrow

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.

Difference Between VLOOKUP Vs HLOOKUP
  • 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 value as 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 VLOOKUP 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.

VLOOKUP Formula

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 is the column we are looking for the result. It is a mandatory argument.
  • : 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.

What Is HLOOKUP?

The HLOOKUP 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

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.
  • : 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.

Example Of VLOOKUP And HLOOKUP

Let us take an example to illustrate VLOOKUP vs 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 VLOOKUP and the HLOOKUP formula.

The output using the VLOOKUP formula is,

Function Data

The output using the HLOOKUP formula is,

Employee Information

Output Interpretation: 

  • VLOOKUP works when the table is vertically set up, i.e., a column-wise table. 
  • 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

VLOOKUP vs HLOOKUP Infographics

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, ), and the formula of HLOOKUP is =HLOOKUP (lookup_value, table_array, row_index_number, ). 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, )=HLOOKUP (lookup_value, table_array, row_index_num, )