Jonayed
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.
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.
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
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
