HLOOKUP and VLOOKUP Function in Excel


Home >> How to Use Excel Tutorial >> Excel Formulas List >> HLOOKUP and VLOOKUP Function in Excel



In this tutorial, we will combine the learnings of HLOOKUP and VLOOKUP function in excel into one. We combine them because you sometimes need both HLOOKUP VLOOKUP at one time in your data lookup process. To make it simpler for that occasional need of yours, we merge their tutorials too in here.


Why do We Need to Learn About the VLOOKUP HLOOKUP Formulas in Excel?


In the data processing process on a spreadsheet file, the data lookup process is a crucial thing to do. Often, there are reference tables in a spreadsheet file from which values are needed in the various parts of our processing. To find the value needed, we should do a data lookup process.

To help with the accuracy and speed of the process significantly, we better know how to do the process automatically too. Of course, it can be done manually but it will take a lot of time. There is also a risk that the result won’t be as accurate.

An example of the process need at work is when we want to input the selling price data of specific products. The input is based on their codes or names. Maybe we need the data when we want to send an invoice for a company’s client. When the invoice is made in a spreadsheet file, the input can refer to the product information reference table in the same file.

Of course, the data lookup process is needed in this case. If we can do it automatically, then the invoice document can be much faster to create and send.

Because of that, you should understand how to use HLOOKUP and VLOOKUP formulas in excel. Your knowledge about the HLOOKUP and VLOOKUP will make your data lookup process in excel much easier to do. This becomes so important when you often use spreadsheet files in your daily work.


What Is The Difference Between VLOOKUP and HLOOKUP?


The definition of VLOOKUP and HLOOKUP in excel is two formulas available to use to find the data you need. The difference is on the perspective when using these formulas of v lookup h lookup in excel. VLOOKUP is used for vertical lookup or using a row perspective. HLOOKUP is used for horizontal lookup or using a column perspective.

A brief explanation about the input needs of excel VLOOKUP and HLOOKUP formula is as follows:


=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)


Note:
  • lookup_value = the lookup value that needs to be found in the first column of your reference table
  • table_array = your reference table cell range
  • col_index_num = the order position of the column on the reference table where the result of this formula will be taken (the order will be counted from the most left position)
  • range_lookup = optional. Determines whether lookup value will be searched exactly or approximately. TRUE for approximate and FALSE for exact



=HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)


Note:
  • lookup_value = reference value to be searched in the first row of your reference table
  • >table_array = your reference table cell range
  • row_index_num = the order position of the row on the reference table from where the result of the formula will be taken (the order will be counted from the most top position)
  • range_lookup = optional. Determines whether lookup value will be searched exactly or approximately. TRUE for approximate and FALSE for exact



How to Use HLOOKUP and VLOOKUP Function in Excel?


The following will explain in detail how to use HLOOKUP and VLOOKUP in excel. Learning the HLOOKUP and VLOOKUP is quite difficult if you just know them. For that, look at the VLOOKUP and HLOOKUP examples given in the screenshots on each step to help your learn easier.


VLOOKUP

  1. Type equal sign ( = ) in the cell where you want to put the result

    HLOOKUP and VLOOKUP Function in Excel - Screenshot of Step 1-1

  2. Type VLOOKUP (can be with large and small letters) and open bracket sign after =

    HLOOKUP and VLOOKUP Function in Excel - Screenshot of Step 1-2

  3. Input the lookup value or cell coordinate where the lookup value is to be searched later in the reference table. Then, type a comma sign ( , )

    HLOOKUP and VLOOKUP Function in Excel - Screenshot of Step 1-3

  4. Drag cursor on the cell range where your reference table is then type a comma sign. Later, the lookup value inputted earlier will be searched in the first column of this reference table

    HLOOKUP and VLOOKUP Function in Excel - Screenshot of Step 1-4

  5. Input the nth order of the column where your lookup result should be found in your reference table. The result will be gotten from where the row in which your lookup value is found combined with this column. If there is more than one fits found for the lookup value, it will get the one on the top

    HLOOKUP and VLOOKUP Function in Excel - Screenshot of Step 1-5

  6. Optional: To determine whether lookup value must be found exactly or just approximately, do this. Input a comma sign and type TRUE/FALSE (can be with large and small letters). TRUE for approximate and FALSE for exact. The definition of approximate is if there is no exact similar value, then it will do this. It will make the smaller nearest value from the lookup value in the reference table’s first column as a reference. As an important note, you must sort the value in the first column in ascending order so the TRUE functions correctly. If there is no input from you in this part, then the input will be assumed as TRUE

    HLOOKUP and VLOOKUP Function in Excel - Screenshot of Step 1-6

  7. Type close bracket sign

    HLOOKUP and VLOOKUP Function in Excel - Screenshot of Step 1-7

  8. Press Enter
  9. The process is done!

    HLOOKUP and VLOOKUP Function in Excel - Screenshot of Step 1-9




HLOOKUP

  1. Type equal sign ( = ) in the cell where you want to put the result

    HLOOKUP and VLOOKUP Function in Excel - Screenshot of Step 2-1

  2. Type HLOOKUP (can be with large and small letters) and open bracket sign after =

    HLOOKUP and VLOOKUP Function in Excel - Screenshot of Step 2-2

  3. Input the lookup value or cell coordinate where the lookup value is to be searched later in the reference table. Then, type a comma sign ( , )

    HLOOKUP and VLOOKUP Function in Excel - Screenshot of Step 2-3

  4. Drag cursor on the cell range where your reference table is then type a comma sign. Later, the lookup value inputted earlier will be searched in the first row of this reference table

    HLOOKUP and VLOOKUP Function in Excel - Screenshot of Step 2-4

  5. Input the nth order of the row where your lookup result should be found in your reference table. The result will be gotten from where the column in which your lookup value is found combined with this row. If there is more than one fits found for the lookup value, it will get the one on the most left

    HLOOKUP and VLOOKUP Function in Excel - Screenshot of Step 2-5

  6. Optional: To determine whether lookup value must be found exactly or just approximately, do this. Input a comma sign and type TRUE/FALSE (can be with large and small letters). TRUE for approximate and FALSE for exact. The definition of approximate is if there is no exact similar value, then it will do this. It will make the smaller nearest value from the lookup value in the reference table’s first row as a reference. As an important note, you must sort the value in the first row in ascending order so the TRUE functions correctly. If there is no input from you in this part, then the input will be assumed as TRUE

    HLOOKUP and VLOOKUP Function in Excel - Screenshot of Step 2-6

  7. Type close bracket sign

    HLOOKUP and VLOOKUP Function in Excel - Screenshot of Step 2-7

  8. Press Enter
  9. The process is done!

    HLOOKUP and VLOOKUP Function in Excel - Screenshot of Step 2-9



Exercise


After learning how to use HLOOKUP and VLOOKUP function in excel, now is the time to do an exercise. It is done so you can better understand the use of these formulas.

Download the HLOOKUP and VLOOKUP exercise file below and answer all the questions. Implement how to use HLOOKUP and VLOOKUP formulas in the exercise!

Then, please download the answer key file if you have done the exercise and want to check your answers. Or probably when you are confused about how to answer the questions!

Link to the exercise file of using HLOOKUP and VLOOKUP formulas:
Download here

Questions

Practice how to use HLOOKUP and VLOOKUP in answering the following questions!
  1. How many is the production target of machine C in October? Use the VLOOKUP function in excel to answer!
  2. How many is the production target of machine D in June? Use HLOOKUP to answer!
  3. How many shift estimations must be run to achieve the January target?

Link to the answer key file of using HLOOKUP and VLOOKUP formulas:
Download here


Additional Note


Want to have more flexibility in the data lookup process, vertically or horizontally, compared to using HLOOKUP and VLOOKUP? Learn how to use the combination of INDEX MATCH formulas here!



Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!


Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!



Follow our tutorial content also on


CONTACT US PRIVACY POLICY TERMS AND CONDITION AFFILIATE DISCLOSURE @Compute Expert