Sometimes we need to use vlookup and return multiple values horizontally in** Excel**. In this article, we are going to show you how to use vlookup and return multiple values horizontally in **Excel** with 3 easy approaches. We have also included some relevant concerns at the end of this article. So, let’s get started.

## 📂 Download Excel File

Download the free workbook from here:

## Learn to Vlookup Return Multiple Values Horizontally in Excel with 3 Approaches

While writing this article, “Vlookup Return Multiple Values”, we’ve considered a dataset that contains approximately **3** columns and **15** rows. In this dataset, we added** Name **and **Purchased Products** to make you better understand. But if you want, you can change the entities and put the values of your own.

**APPROACH**

### 1. Using SMALL, INDEX, and IF Functions

You can use **SMALL**, **INDEX**, and **IF **functions combined with to vlookup and return multiple values horizontally in **Excel**. Using these functions is not difficult. You can easily use them by following the given steps.

⬇️⬇️ **STEPS** ⬇️⬇️

- Initially, select cell
**D17**. - Secondly, write the given formula on that particular cell.

**=INDEX($C$6:$C$15, SMALL(IF($C$17=$B$6:$B$15,ROW($B$6:$B$15)-ROW($B$6)+1), COLUMN(A1)))**

**🔨 Formula Breakdown**

Here,

👉 **ROW($B$6:$B$15)-ROW($B$6)+1) **implies the number of rows for all data available from cells **B6 **to **B15**.

👉 **IF($C$17=$B$6:$B$15,ROW($B$6:$B$15)-ROW($B$6)+1) **means the formula checks the criteria only for James. If a match is sorted, the formula will go to the corresponding row number, otherwise, it’ll give **FALSE**.

👉 **SMALL(IF($C$17=$B$6:$B$15,ROW($B$6:$B$15)-ROW($B$6)+1), COLUMN(A1))) **gives the smallest row number found from the one step earlier.

- Thirdly, press
**Enter**.

- Fourthly, using the
**Fill Handle**, drag the formula to the remaining cells horizontally. - Finally, you will get the desired results.

Truly, using these functions to vlookup and return multiple values horizontally in **Excel **is very easy to apply.

**APPROACH**

### 2. Applying FILTER Function

Another way to vlookup return multiple values horizontally is to apply the **FILTER **function along with the **TRANSPOSE **function. You have to maintain the following steps.

⬇️⬇️ **STEPS** ⬇️⬇️

- Primarily, click on cell
**D17**. - Secondly, put the given formula down to that cell.

**=TRANSPOSE(FILTER(C6:C15, C17=B6:B15))**

- Then tap
**Enter**. - Again using the
**Fill Handle**icon, drag the formula to the remaining cells horizontally. - Eventually, you will see the results for the entire row.

Undoubtedly, applying this method to vlookup and returning multiple values horizontally in **Excel** is a handy and user-friendly technique introduced in **Microsoft 365**.

**📕 Read More: 5 Ways to Extract Filtered Data into Another Excel Sheet**

**APPROACH**

### 3. Using IF, COUNTIF, INDEX, and MATCH Functions

You can vlookup and return values horizontally in **Excel**, by using **IF**, **COUNTIF**, **INDEX**, and **MATCH **functions. You just have to maintain the following steps.

⬇️⬇️ **STEPS** ⬇️⬇️

- Primarily, click on cell
**D17**. - Secondly, write the given formula down to that cell.

**=IF(COLUMN()-3<=COUNTIF($B$6:$B$15,$C17),INDEX($C$6:$C$15,MATCH($C17,$B$6:$B$15,0)+COLUMN()-4),””)**

**🔨 Formula Breakdown**

Here,

👉 **MATCH($C17,$B$6:$B$15,0) **refers to the **MATCH **function of the cell name James.

👉 **INDEX($C$6:$C$15,MATCH($C17,$B$6:$B$15,0)+COLUMN()-4) **implies that **IF **function argument gives the first output based on the corresponding row number.

- Now tap
**Enter**.

- Thirdly, use the
**Fill Handle**to drag the formula horizontally. - Finally, you will get the result.

Truly this is a very simple procedure to vlookup and returns multiple values horizontally in **Excel**.

**📕 Read More: Find MAX of Multiple Values Using VLOOKUP Function in Excel**

## How to VLOOKUP to Return Values from Multiple Columns in Excel

If you are looking for the vlookup to return multiple columns in **Excel**, then this part is for you. You can easily do it by using the **VLOOKUP **function. This function is a unique feature of **Excel**. You have to maintain the given steps to achieve this method.

**⬇️⬇️ STEPS ⬇️⬇️**

- Firstly, select cells
**G6**to**J6**. - Secondly, write the following formula on that cell.

**=VLOOKUP(G6,B6:E15,{2,3,4},FALSE)**

**🔨 Formula Breakdown**

Here,

👉 **{2,3,4} **refers to the column number.

👉 **VLOOKUP(G6,B6:E15,{2,3,4},FALSE) **implies that we will get the exact values corresponding to the cell.

- Thirdly, press
**Enter**.

- Now use the
**Fill Handle**to drag the formula down. - Consequently, you will get the results.

Finally, we can say that **using the VLOOKUP function** to return multiple columns in **Excel **is a very useful and easy method.

**📕 Read More: VLOOKUP to Find Duplicates in Two Columns in Excel**

## How to Use INDEX-MATCH Formula to Return Multiple Values Vertically

If you intend to index match multiple values vertically in **Excel**, then you are in the exact place. We are going to show you how you can make it work.

**⬇️⬇️ STEPS ⬇️⬇️**

- Initially, select cell
**F6**. - Now write the given formula there.

**=IFERROR(INDEX($C$6:$C$15,SMALL(IF($E$6=$B$6:$B$15,ROW($B$6:$B$15)-ROW($B$6)+1),ROW(1:1))),””)**

**🔨 Formula Breakdown **

Here,

👉 **$C$6:$C$15 **refers to the range of values.

👉 **$E$6 **is the criteria.

👉 **ROW(1:1) **implies the vertical return of the values.

- Thirdly tap
**Enter**.

- Now drag the formula using the
**Fill Handle**. - Finning, the required results will appear to you.

Lastly, this is a very simple method to work on.

**📕 Read More: 8 Ways to VLOOKUP to Return Multiple Values Vertically in Excel**

## How to Return Values Based on Single Criteria in Excel

You can return based values on single criteria in **Excel**. You just have to follow the steps given below.

**⬇️⬇️ STEPS ⬇️⬇️**

- To do this, first, you need to click on cell
**F6**. - Secondly, you need to write the following formula down on the cell.

**=TEXTJOIN(“,”,TRUE,IF($B$6:$B$15=E6,$C$6:$C$15,””))**

**🔨 Formula Breakdown**

Here,

👉 **$C$6:$C$15 **refers to the lookup array.

👉 **$B$6:$B$15=E6 **implies that we made a check of **E6 **cell to the **Purchased Products **column.

- Thirdly you have to press
**Enter**.

- Fourthly you need to drag the formula using the
**Fill Handle**. - Finally, you will get the required values corresponding to the cells.

Undoubtedly, this is a very useful and handy technique to return values based on single criteria in **Excel**.

**📕 Read More: What Is Table Array in VLOOKUP in Excel?**

## 📄 Important Notes

While performing the processes mentioned above, you need to be aware of these things:

🖊️ You need to be careful while using the Excel mentioned above** **functions.

🖊️ In the given formula, you need to define the required cells carefully.

🖊️ If you have any confusion about those **Excel **functions, then you need to learn them first.

## 📝 Takeaways from This Article

If we summarize the whole article, we have got some points.

📌 Firstly, we used **SMALL**, **INDEX**, and **IF **functions to **VLOOKUP** and return multiple values horizontally in **Excel**.

📌 Secondly, we have applied the **FILTER **function.

📌 Thirdly, we used the **COUNTIF **function.

📌 Fourthly, we tried to show you the procedure to **VLOOKUP **and return multiple columns in **Excel**.

📌 Fifthly, we matched the index return multiple values vertically in **Excel**.

📌 Finally, we have tried to show you to return values based on single criteria in **Excel**.

## Conclusion

We wish that you would be able to VLOOKUP and return multiple values horizontally in **Excel** using this article. As several methods have been shown here, you can easily follow which one is best suited for you. If there is any skepticism in your mind, feel free to ask me in the comment. To learn more about **Excel** tactics, we recommend you to visit our website **www.ExcelDen.com**.

## Related Articles

**7 Ways to Use VLOOKUP to Compare Two Lists****6 Solutions If VLOOKUP Returns #N/A When Match Exists in Excel****5 Examples to Use IF and VLOOKUP as Nested Function in Excel****2 Ways to Use VLOOKUP Function with Two Lookup Values**