VLOOKUP vs HLOOKUP

Difference Between VLOOKUP Vs HLOOKUP

The difference between VLOOKUP vs HLOOKUP is that VLOOKUP references columns, while HLOOKUP references rows. VLOOKUP function in Excel, also known as The Vertical-LOOKUP and HLOOKUP functions in Excel, also known as Horizontal-LOOKUP, are powerful referencing tools that allow users to match data from a table array and display the output. VLOOKUP is used to search for a specific value in the first column of a table and return a corresponding value from a different column in the same row. HLOOKUP searches for a value in the first row of a table and returns a corresponding value from a different row in the same column.

These functions are incredibly useful for organizing and analyzing large sets of data in Excel. By mastering the Vertical-LOOKUP and HLOOKUP functions, users can save time and increase their productivity when working with complex spreadsheets.

Key Takeaways
  • The VLOOKUP and HLOOKUP are functions we can use to retrieve the data using the lookup_value as the argument.
  • Remember, we can retrieve column-wise data using the VLOOKUP or Vertical LOOKUP function.
  • Similarly, we can use the HLOOKUP or Horizontal LOOKUP function to retrieve row-wise data.
  • Both functions are the same that have the same arguments.
  • The col_index_num argument for the VLOOKUP function and the row_index_num argument for the HLOOKUP function differ in both functions.
  • If the range_lookup value is 0 or 1, it will execute as 1 for TRUE as the default input to return the approximate match.

What Is VLOOKUP?

Before learning the formula and difference between VLOOKUP vs HLOOKUP, we need to understand what is VLOOKUP. The “V” in VLOOKUP stands for vertical movement, and the “LOOKUP” stands for fetching data from a specific source. In Excel, VLOOKUP searches for values in the columns of a defined table of an array and returns the value in the same column where we want it.

VLOOKUP Formula And Example

The syntax of the VLOOKUP function in Excel is

Vlookup Formula
  • lookup_value = The lookup_value refers to the value searched in the first row of the table.
  • table_array = The argument returns the data with reference to the name of the table_array.
  • col_index_number = The col_index_number refers to the column number in the “table_array” where the value is located and returned.
  • When the “col_index_number” is set to 1, the function returns the value from the table_array’s topmost column. Similarly, when we set the “col_index_number” as 2, the function returns the value from the second column of the table_array.
  • range__lookup = The “range_lookup” parameter determines whether the function should perform an approximate or exact match. A value of TRUE (1) indicates an approximate match, while a value of FALSE (0) indicates an exact match. The function will return the #N/A error if there is no match.

Example

Shawon’s retail store intends to get the salary details of each of its employees. It can therefore use the VLOOKUP Function for this purpose with the help of the Excel sheet containing the names and salaries of employees.

  • Row 1 shows the heading
  • Row 2 names of employees are provided
  • Row 3 displays their salaries.

Using the VLOOKUP function, we can fetch the whole table:

Step 1: Lookup value is the cell address of Name, i.e., A2.

Vlookup Example - Intro

Step 2: The table array is the entire table, A2:E3, from row 2 to row 3. It is because row 1 indicates employee salary status.

Step 3: The column index number is 1st per table array.

Step 4: The range lookup is FALSE to search for the exact match.

=VLOOKUP(A2,A2:E3,1,FALSE)

We can use the same formula in every cell to fetch the value for the entire table.

Vlookup Example - Step 4

What Is HLOOKUP?

The “H” in HLOOKUP stands for horizontal movement, and the “LOOKUP” stands for fetching data from a specific source. In Excel, HLOOKUP searches for values in the rows of a defined table of an array and returns the value in the same column where we want it.

HLOOKUP Formula And Example

The syntax of the HLOOKUP function in Excel is

Hlookup Formula
  • lookup_value = It is the value fetched from the topmost row of the table.
  • table_array = It is the reference or name of the table array from which the data is fetched. The lookup value must be in the topmost row of the table.
  • row_index_number = It indicates the row number in the table_array. Here, we can match and return the value. If row_index_number equals 1, it will return the value from the topmost row of the table_array. Likewise, if row_index_number is equal to 2, it will return the value from the 2nd row of the table_array, and so on.
  • range__lookup = The range that looks up if the value is TRUE or FALSE. TRUE (that is, 1) stands for the approximate match. Whereas FALSE (0) stands for the exact match value. If neither happens, the function returns the “#N/A” error.

Example

The following image illustrates the price of fruits. Our objective is to retrieve the table that displays the fruit’s price by utilizing the HLOOKUP function.

In the table, columns A and B shows the Items and Price, respectively.

Hlookup Example - Intro

The steps to retrieve the price of items are as follows:

Step 1: To obtain the desired outcome, it is imperative that we input the following formula into cell B9: =HLOOKUP (B1, A1:B5, 2, 0). To begin, we must first enter the lookup value that we wish to search for in the topmost row. As we are seeking the price, we will input B1 as the lookup value.

Step 2: Select the table array; we must specify the range from the starting cell address to the end cell address of the entire table as A1:B5.

Step 3: To retrieve the desired value, include the row index number. In this case, as the item count begins on row 2, input the index number as 2.

Step 4: To achieve an exact match, it is necessary to set the range lookup value as FALSE or ‘0’. 

The complete formula entered is: =HLOOKUP (B1, A1:B5, 2, 0)

Step 5: To complete the previous step, input each value and press Enter.

We can see the outcome in cell B9. This demonstrates the successful retrieval of an exact match by the HLOOKUP function from the designated table array and value.

Step 6: Press the Enter and drag the formula to cell B10.

Hlookup Example - Step 6

VLOOKUP Vs HLOOKUP – Key Differences

Let us look at the VLOOKUP vs HLOOKUP – Key Differences to understand the functions better:

  • The VLOOKUP function finds values from a vertically arranged table. But, the HLOOKUP function finds values from a horizontal table.
  • The most used VLOOKUP function allows users to search for a specific value in a table and return a corresponding value from the same row. However, its counterpart, HLOOKUP, is not as commonly used.
  • The output generated is the same but with a different aspect, horizontally and vertically.
  • The syntax of VLOOKUP function is =VLOOKUP (lookup_valuetable_arraycol_index_number, [range_lookup]), and the syntax of HLOOKUP function is =HLOOKUP (lookup_valuetable_arrayrow_index_number, [range_lookup]).
  • The only difference between the formulas of VLOOKUP and HLOOKUP functions is: row and column.
  • VLOOKUP finds the data in the leftmost column.
  • HLOOKUP is a function that allows us to search for data within a specified range, specifically in the bottom-most rows.

VLOOKUP vs HLOOKUP Comparative Table

BasisVLOOKUPHLOOKUP
DefinitionVLOOKUP function is used to fetch data vertically.HLOOKUP function is used to fetch data horizontally.
UsageVLOOKUP function is the most frequently used function in Excel.HLOOKUP function is the rarely used function in Excel.
OutputThe output is displayed column-wise.The output is displayed row-wise.
Type of tableA vertical table is used.A horizontal table is used.
Search DataThe data is searched from the leftmost column.The data is searched from the bottommost row.
Syntax=VLOOKUP (lookup_value, table_arraycol_index_number, [range_lookup])=HLOOKUP (lookup_valuetable_arrayrow_index_number, [range_lookup])

This has been a guide to VLOOKUP vs HLOOKUP. Here we discuss the meaning, formulas and key differences between them along with a comparative table. You may learn more from the following articles –

Rotate Pie Chart In Excel

Count Rows In Excel

Dynamic Named Range In Excel

Reader Interactions

Leave a Reply

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