3 Quick Ways to Vlookup and Return Multiple Values Horizontally

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.

Dataset to vlookup return multiple values horizontally in Excel

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.

using SMALL, IF functions to vlookup return multiple values horizontally in Excel

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

result of SMALL, IF functions to vlookup return multiple values horizontally in Excel

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.

Using FILTER function to vlookup return multiple values horizontally in Excel

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.

Use of COUNTIF function

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

Result of uding COUNTIF function

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.

Using VLOOKUP function

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

Result of VLOOKUP function

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.

Using IFERROR function

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

Result of using IFERROR function

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.

Result of using TEXTJOIN function

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

(Visited 55 times, 1 visits today)
Jonayed

Jonayed

Hello! My name is Jonayed. I've completed my graduation from BUET in Naval Architecture & Marine Engineering. As Excel is very interesting to me, I like to do various types of research regarding Excel. My inquisitive nature drives me to find solutions to different types of problems with Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo