How to VLOOKUP and Return Multiple Values in Excel (8 Methods)

Whenever we need to pull a value from another worksheet or within the same worksheet, we can’t help but think of the VLOOKUP function in Excel. But the main problem with the VLOOKUP function is that it is only designed to return a single value once. So, we have to think a bit out of the box to vlookup multiple values with this function which we will describe in this article.

We can also use the other Excel functions such as INDEX, SMALL, MATCH, ROW, COLUMNS, etc., suitably combining them to vlookup and return multiple values. We can also use several Excel features like the Advanced Filter, the AutoFilter, and the Format as Table tool. So, in this tutorial we are going to discuss a total of 1 method using the VLOOKUP function, 4 methods using the other Excel functions, and 3 methods using different Excel tools and options to vlookup and return multiple values in Excel with ease.


Download Practice Workbook

You are recommended to download the Excel file and practice along with it.


8 Methods to VLOOKUP and Return Multiple Values in Excel

In this tutorial, we will be using an Employee Database to demonstrate all 8 methods throughout the article.

vlookup multiple values dataset

So, without having any further discussion, let’s get into all the 8 methods one by one.


1. Return Multiple Values Using the VLOOKUP Function in Excel

We know that the VLOOKUP function can return only one value at a time. But we need to return multiple values. Yes, there are other options available to do so. But if you want to use particularly the VLOOKUP function, don’t lose hope. There’s a way out. In this section, you will learn how you can use the VLOOKUP function to return multiple values by tweaking your dataset a bit. The functions that we will be using in this method are COUNTIF and VLOOKUP. Now follow the steps below:

🔗 Steps:

First of all, we need to make all the department names unique. To do so,

❶ Select cell E5.

❷ Now type the formula

=B5&COUNTIF(B5:B$13,B5)
within the cell.

❸ After that, hit the ENTER button.

Return Multiple Values Using the VLOOKUP Function in Excel

❹ The drag the Fill Handle icon down to the Dept._Unique column.

❺ Now select cell C16 and type the formula

=VLOOKUP(B16,E5:F13,2,0)
with it.

❻  Afterwards, hit the ENTER button.

❼ Finally, drag the Fill Handle icon to the end of the Employee column.

That’s it.


2. Vlookup and Pull Multiple Values Vertically in Excel

Suppose, you want to find out the number of employees working under the Engineering department and organize those names vertically in columns. If so then follow the steps below to learn how to do it.  The functions that we will be using in this method are INDEX, SMALL, MATCH, and ROW.

🔗 Steps:

❶ First of all, select cell F5 ▶ to store the formula result.

❷ After that, type the formula

=INDEX($C$5:$C$13, SMALL(IF(($E$5=$B$5:$B$13), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), ""), ROWS($A$1:A1)))
  within the cell.

❸ Then press the ENTER button.

Vlookup and Pull Multiple Values Vertically in Excel

❹ Now drag the Fill Handle icon downward in the Employee column.

␥  Formula Breakdown

  • ROW($B$5:$B$13) ▶ returns the row number in array form: {5;6;7;8;9;10;11;12;13}
  • MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)) ▶ converts the previous array into the following: {1;2;3;4;5;6;7;8;9}
  • IF(($C$15=$B$5:$B$13), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), “”) ▶ returns the row number that matches the condition, otherwise returns null: {“”;2;””;4;””;””;””;””;9}
  • SMALL(IF(($E$5=$B$5:$B$13), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), “”) ▶ returns the first small number within the array: {“”;2;””;4;””;””;””;””;9}
  • INDEX($C$5:$C$13, SMALL(IF(($E$5=$B$5:$B$13),MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), “”) ▶ returns the employee names based on the row index number returned by the SMALL.

3. Vlookup and Return Multiple Values Horizontally in Excel

Let’s say, now you want to find out the number of employees working under the Engineering department and organize those names horizontally in rows. The functions that we will be using in this method are INDEX, SMALL, MIN, and ROW.

🔗 Steps:

❶ First of all, select cell C16 ▶ to store the formula result.

❷ After that, type the formula

=INDEX($C$5:$C$13, SMALL(IF($C$15=$B$5:$B$13, ROW($B$5:$B$13)-MIN(ROW($B$5:$B$13))+1, ""), COLUMNS($A$1:A1)))
within the cell.

❸ Then press the ENTER button.

Vlookup and Return Multiple Values Horizontally in Excel

❹ Now drag the Fill Handle icon to the right side of the Employee row.

␥  Formula Breakdown

  • ROW($B$5:$B$13) ▶ returns the row number in array form: {5;6;7;8;9;10;11;12;13}
  • SMALL(IF($C$15=$B$5:$B$13,ROW($B$5:$B$13)-MIN(ROW($B$5:$B$13))+1, “”) ▶ returns the first small number within the array.
  • INDEX($C$5:$C$13, SMALL(IF($C$15=$B$5:$B$13,ROW($B$5:$B$13)-MIN(ROW($B$5:$B$13))+1, “”) ▶ returns the employee names based on the row index number returned by the SMALL.

4. Vlookup and Get Multiple Values in Excel with Criteria

In this section, we will return all the employee names in the Employee column who work under the Engineering department and also work in the Morning shift. The functions that we will be using in this method are INDEX, SMALL, IFERROR, and ROW.

🔗 Steps:

❶ First of all, select cell H5 ▶ to store the formula result.

❷ After that, type the formula

=IFERROR(INDEX($C$5:$C$13,SMALL(IF(1=((--($F$5=$B$5:$B$13)) *(--($G$5=$D$5:$D$13))), ROW($C$5:$C$13)-4,""), ROW()-4)),"")
within the cell.

❸ Then press the ENTER button.

Vlookup and Get Multiple Values in Excel with Criteria

❹ Now drag the Fill Handle icon downward in the Employee column.

␥  Formula Breakdown

This formula is quite similar to the previous two formulas. Here are the exceptions:

  • ROW($C$5:$C$13)-4 ▶ returns the row numbers within the array: {5;6;7;8;9;10;11;12;13}. Here, the number 4 refers to the row number on which the column header Employee resides.
  • ROW()-4 ▶ here the number 4 refers to the previous row number before the output row begins.
  • IFERROR ▶ here the IFERROR function is used to synthesize a customized output if any error occurs.

5. Vlookup and Extract Multiple Values in Excel (All in One Cell)

Now we will put together all the employee names within the same cell who work under the Engineering department. The functions that we will be using in this method are TEXTJOIN and IF.

🔗 Steps:

❶ First of all, select cell H5 ▶ to store the formula result.

❷ After that, type the formula

=TEXTJOIN(",",TRUE,IF($B$5:$B$13=$C$15,$C$5:$C$13,""))
within the cell.

❸ Then press the ENTER button.

Vlookup and Extract Multiple Values in Excel (All in One Cell)

That’s it.

␥  Formula Breakdown

Here, the IF function returns all the employee names that match the condition and the TEXTJOIN function joins all the employee names returned by the IF function.


6. Vlookup to Return Multiple Values in Excel Using AutoFilter

If you want to avoid using Excel formulas, then you can vlookup through data tables and pull out multiple values by using the AutoFilter feature. Here, we will try to sort out all the employee names who work under the Engineering department.

🔗 Steps:

❶ First of all, select the entire data table.

❷ Then go to the Data ribbon.

❸ After that, click on the Filter option.

❹ Now click on the filter icon just below the Department column header.

Vlookup to Return Multiple Values in Excel Using AutoFilter

❺ Now tick mark on the Engineering option from the pop-up menu.

❻ Then hit the OK button.

Bravo! You are done with it. Now you will the result as follows:


7. Vlookup and Extract Multiple Values in Excel Using Advanced Filter

There’s a feature called Advanced Filter in Excel which you can use to lookup through a data table vertically and extract multiple values all at the same time. In the following example, we picked up the Engineering department under the Department column header. Which we will be using to search throughout the data table to find out which employees work under the Engineering department. Now, here are the steps to follow:

🔗 Steps:

❶ First of all, select the entire data table.

❷ Then go to the Data ribbon.

❸ After that, click on the Advanced option.

Vlookup and Extract Multiple Values in Excel Using Advanced Filter

❹ Once you are done with the last step, a dialog box named Advanced Filter will appear on the screen.

❺ Now enter

$B$4:$C$13
within the List range: bar and
'Advanced Filter'!$E$4:$E$5
within the Criteria range: bar.

❻ After that, you can hit the OK button.

When you are done with all the previous steps, you will get the output as follows:


8. Vlookup to Grab Multiple Values in Excel Using Format as Table

You can use this method as an alternative to the AutoFilter and Advanced Filter option. In this method, we will use the Format as Table option to sort all the employee names under the Engineering category. Here are the steps to follow:

🔗 Steps:

❶ First of all, select the entire data table.

❷ Then go to the Home ribbon.

❸ After that select Format as a Table option.

❹ Then select any of the table styles as you like.

Vlookup to Grab Multiple Values in Excel Using Format as Table

Now your data table will look something like the following image.

❺ At this phase, click on the Filter icon under the Department column header.

❻ From the pop-up menu, tick mark only on the Engineering option.

❼ Then click on the OK button.

When you are done with all the previous steps, your result should look like this:


Things to Remember

📌 The VLOOKUP function can’t bring more than one value alone.

📌 Be careful about the syntax of the functions.

📌 Insert the data ranges carefully into the formulas.


Conclusion

To wrap up, we have discussed 8 distinct methods to vlookup and return multiple values in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap.


Related Readings

Mrinmoy

Hi! This is Mrinmoy. I write about Excel and VBA stuff for Exceldemy. I have my graduation completed from Khulna University of Engineering & Technology, Bangladesh. I have an immense interest in Datascience and Machine Learning. I like to play guitar and watch Tedtalks in my leisure time. I used to be an indie filmmaker and a wedding photographer. I'm liberal to any kind of constructive criticism and inclined towards expanding my learnings.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo