How to Use VLOOKUP and HLOOKUP Combined Formula in Excel?

Get FREE Advanced Excel Exercises with Solutions!

So, This tutorial will demonstrate how to use VLOOKUP and HLOOKUP Combined formulas together in Excel formulas. Moreover, we can find specific data or formulas or groups of formulas or data by using these functions. If you use these functions, it will search the particular data you have inserted. In case, you want to find a certain column or group of columns in the worksheet, then naming the table array is very useful. After naming a table array, you can find it with selected cells under it only by searching by the proper name. So, it shortens our work and is essential for us to learn to use it quickly.


Overview of VLOOKUP Function

  • Description

In case, you are looking up a value vertically in Excel, you need to use the VLOOKUP function. Undoubtedly, it is a very useful built-in function in Excel that is under the Lookup function category. The VLOOKUP Function is mainly used when you have to find a table or some info or any data by row.

  • Generic Syntax

=VLOOKUP (Lookup_Value, Table_Range, Column_Index, [Range_Lookup])

  • Argument Description
Arguments Requirements Explanation
Lookup_Value Required The value which you will look up vertically from the first column of the Table_Range.
Table_Range Required Defines the range of the VLOOKUP function.
Column_Index Required The column number from the Table_Range by which the matched value will be returned.
Range_Lookup Optional It’s an optional argument. Write False to get an Exact Match and True for an approximate match. True is the default parameter while omitting this argument.

Example:

Learing Basics of VLOOKUP to learn VLOOKUP & HLOOKUP Combined Together in Excel


Overview of HLOOKUP Function

  • Description

If you want to look up a value horizontally in Excel, you need to use the HLOOKUP function. It is also a built-in function in Excel, which is under the Lookup function category. Moreover, the HLOOKUP or Horizontal Lookup function is generally used to extract data from a table or an array based on searching for a specified value in the topmost row and the corresponding column.

  • Generic Syntax

=HLOOKUP (Lookup_Value, Table_Range, Row_Index, [Range_Lookup])

  • Argument Description
Arguments Requirements Explanation
Lookup_Value Required The value which you will look up vertically from the first column of the Table_Range.
Table_Range Required Defines the range of the HLOOKUP function.
Row_Index Required The row number from the Table_Range by which the matched value will be returned.
Range_Lookup Optional It’s an optional argument. Write False to get Exact Match and True for an approximate match. True is the default parameter while omitting this argument.

Example:

Learning Basics of HLOOKUP Function for learning VLOOKUP & HLOOKUP Combined Together in Excel


How to Use VLOOKUP & HLOOKUP Combined Formula Together in Excel: Step-by-Step

If you follow the steps correctly, you should learn how to use both VLOOKUP and HLOOKUP combined formulas together in Excel to ease the work. So, we have to follow the below steps to learn this method.
Steps:

  • First, arrange the proper dataset that you want to use. Now, we have the Salesperson in Column B and two years 2021 and 2022 in Columns C and D.

Inserting Formula to VLOOKUP & HLOOKUP Combined Together in Excel

  • Next, in cell D14, insert the following formula.
=VLOOKUP(B14,B5:D11,2,0)

  • After that, you will get the desired result.

🔎 How Does the Formula Work?

  • HLOOKUP(C15, B4:D5,2, FALSE): It represents the selected criteria the function will search.
  • VLOOKUP(B15, B6:D12, HLOOKUP(C15, B4:D5,2, FALSE), FALSE): In this portion, the first part represents the selected data table and the second part represents the desired criteria for searching.


How to Use IF Statement with VLOOKUP Function

We will learn how to combine the IF, VLOOKUP & HLOOKUP functions. The If function gives logical comparison, and VLOOKUP & HLOOKUP functions find specific data from a certain range. The process to learn this method is as follows.

We can ease our work by combining the IF statement with the VLOOKUP function. So, we have to follow the following steps to learn this whole process.

Steps:

  • Now, in cell D14, insert the following formula.
=IF(VLOOKUP(B14,$B$4:$D$11,3,FALSE )>=30000, VLOOKUP(B14,$B$4:$D$11,3,FALSE)*20%, VLOOKUP(B14,$B$4:$D$11,3,FALSE)*10%)

Inserting if statement with vlookup function

  • Finally, you will get the desired result.

🔎 How Does the Formula Work?

  • VLOOKUP(B14,$B$4:$D$11,3, FALSE): It represents the selected columns of the worksheet.
  • IF(VLOOKUP(B14,$B$4:$D$11,3, FALSE )>=30000, VLOOKUP(B14,$B$4:$D$11,3, FALSE)*20%, VLOOKUP(B14,$B$4:$D$11,3, FALSE)*10%): In this portion, the required condition is presented along with the selected range on the condition that will be applied.


How to Use IF Statement with HLOOKUP Function

We can also do the same work by combining the IF statement with the HLOOKUP function. Now, to do so, we have to follow the following steps.

Steps:

  • Next, in cell B14 insert the following formula.
=HLOOKUP(B13,B4:D11,IF(D10>30000,7))

  • Finally, you will get the desired result.

🔎 How Does the Formula Work?

  • IF(D10>30000,7): It represents the proper condition of this function.
  • HLOOKUP(B13, B4:D11, IF(D10>30000,7)): In this case, the required condition is presented along with the selected range on the condition that will be applied.


Download Practice Workbook

You can download the practice workbook from here.


Conclusion

Henceforth, follow the above-described methods. Thus, you will be able to learn the basics of the VLOOKUP and HLOOKUP combined formulas. So, let us know if you have more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


<< Go Back to Lookup | Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Siam Hasan Khan
Siam Hasan Khan

Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

13 Comments
  1. Great instruction on how to use both VLOOKUP and HLOOKUP functions! I like this.

  2. its very good example

  3. I’d love to have a few exercises to apply this knowledge in order to assimilate it properly. Thanks for considering this request.

  4. I can’t believe I just understood this…Appreciate the clear example, you are a great instructor!

  5. Hi Kawser.. thanks for the blog post on doing a 2-way lookup using VLOOKUP and HLOOKUP together. The weakness is that you have to convert the month reference to a number with a helper table. As you mentioned in your conclusion, a more direct approach would be to combine INDEX and MATCH for a 2-way lookup as follows (using your data set as example):
    =INDEX($B$2:$E$9,MATCH($H$4,$A$2:$A$9,0),MATCH($H$5,$B$1:$E$1,0))
    In this approach, no helper table is required to generate the month number for the column. The MATCH function does it for you. So, arguably it is more efficient. Either way works.. nice to know multiple methods to solve a problem. Thanks for your insights!

  6. Thanks for the well written and very helpful article by explaining the concepts clearly with examples.

  7. hello sir i am microsoft certified trainer from Gujarat India, i am using your method and some example to explain formula during my lecture , thanks for your blog , lots of thanks – rajesh bhonkiya

  8. Hi Kawser, I have always enjoyed your blog posts, they are insightful and helpful. I have figured an alternative to Wayne’s suggestion – that would be to use a combination of vlookup and match: =VLOOKUP(H4,A1:E9,MATCH(H5,B1:E1,0)+1,0), without using an additional match.

    This uses the match to return the column number of the selected month and subsequently vlookup to the product name based on the column number to obtain the product name for the given month.

    Thanks to your blog posts, I have developed a strong interest in writing and understanding excel formulas.
    Cheers!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo