How to Use VLOOKUP and HLOOKUP Combined Formula in Excel

Steps:

  • 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

  • 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%): 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

Steps:

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

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


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

Get FREE Advanced Excel Exercises with Solutions!
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