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.

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

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,

The arguments of the VLOOKUP formula are,

• lookup_value: The value for which we are trying to retrieve the result from the table_array (2^nd argument). It is a mandatory argument.
• table_array: It 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.

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

The arguments of the HLOOKUP formula are,

• lookup_value: The value for which we are trying to retrieve the result from the table_array (2^nd argument). It is a mandatory argument.
• table_array: It 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.

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”.

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,

The output using the HLOOKUP formula is,

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.

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.

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

• VLOOKUP Examples
• Examples of HLOOKUP
• VLOOKUP Error
• Absolute in Excel