In this article, we will see how to use VLOOKUP and HLOOKUP Combined functions together in Excel formulas. Here we will use VLOOKUP and HLOOKUP combinedly to solve a problem. The basics of VLOOKUP and HLOOKUP will be shown. We will be using an example to find out a specific result using the combination of VLOOKUP and HLOOKUP.

## Basics of VLOOKUP and HLOOKUP

Before solving the problem using combined VLOOKUP and HLOOKUP, let`s get some basic idea about the functions.

### VLOOKUP Description

When you want to look up a value vertically in Excel you need to use the VLOOKUP function. It is a built-in function in Excel which is under the Lookup function category.

### Syntax

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

### Arguments

**Lookup_Value **– the value which you will look up vertically from the first column of the **Table_Range**.

**Table_Range**– Defines the range of the VLOOKUP function.

**Column_Index- **The column number from the** Table_Range **by which the matched value will be returned.

**Range_Lookup**– 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

### HLOOKUP Description

When you want to look up for 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.

### Syntax

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

### Arguments

**Lookup_Value**– Value which will be found out horizontally from the first row of the **Table_Range**.

**Table_Range**– Defines the range of the HLOOKUP function.

**Row_Index**– The row number from the **Table_Range **by which the matched value will be returned.

**Range_Lookup**– 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

## VLOOKUP & HLOOKUP Combined Together in an Excel Formula

Let`s look into an example where different products name with its monthly sales is given. We want to insert a formula in the worksheet where, by indicating the product and months name, the formula will give us the desired result.

For finding the result. Here we will be using the VLOOKUP and HLOOKUP functions together. Firstly, let`s look into the HLOOKUP function. In a different place of your worksheet insert months name along with the column number. By using the HLOOKUP function we will find the Column number first. In cell **H6**, write down the formula,`<code>`

**=HLOOKUP(H5,B14:E15,2,FALSE)** It will look for the month name in the range **B14: E15**. The row index is given here as 2. So, the formula will return the value 5.

Now, we will combine the **HLOOKUP** function with the **VLOOKUP** function to find the monthly sales of a product. In cell **H7**, write down the formula `<code>`

**=VLOOKUP(H4,A2:E9,HLOOKUP(H5,B14:E15,2,FALSE), FALSE) **and press Enter. It will evaluate the result that we wanted. As we indicate **Product name** **Toaster** and **Month name** **April**. So, it will give us the result 1700 in cell **H7**. By changing the names in cell **H4** and **H5 **we will get different results.

Here the formula, `<code>`

**=VLOOKUP(H4,A2:E9,HLOOKUP(H5,B14:E15,2,FALSE), FALSE) **lookup for the value Toaster which is in cell H4 in the range **A2: E9**. In the function of **VLOOKUP**, the formula `<code>`

**HLOOKUP(H5,B14:E15,2,FALSE) **is given as the **Column_Index**. So instead of defining **Column_Index** with numbers, you are doing this with the **HLOOKUP **function. The **False** arguments will give you the exact match.

Let`s check this formula for a different product and month. Now we will choose the product **Blender** and month **February** to find the monthly sales of the month **February. **In cell **H4** and **H5**, write **Blender** and **February**.

## Conclusion

In this article, we saw the basics of **VLOOKUP **and **HLOOKUP** functions. The **VLOOKUP** and **HLOOKUP** combined formula was shown with an example. Instead of **VLOOKUP** and **HLOOKUP**, we can also use the **INDEX **and **MATCH** functions based on different operations. Hope you will like this article. Comment below to ask any questions and let us know your opinions on our articles.

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!