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%)
- You will get the desired result.
How Does the Formula Work?
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?
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!
Great instruction on how to use both VLOOKUP and HLOOKUP functions! I like this.
Thanks for your feedback, George!
its very good example
Thanks for your feedback.
I’d love to have a few exercises to apply this knowledge in order to assimilate it properly. Thanks for considering this request.
Hi,
You can try to use the concepts in real-world problems.
Thanks.
I can’t believe I just understood this…Appreciate the clear example, you are a great instructor!
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!
Thank you so much for your input, Wayne! Hope this input helps my readers 🙂
Best regards
Kawser Ahmed
Thanks for the well written and very helpful article by explaining the concepts clearly with examples.
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
A great pleasure for me. Thank you.
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!