Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Return Multiple Values with VLOOKUP in Excel (8 Ways)

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 return multiple values with this function which we will describe in this article. 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 return multiple values with VLOOKUP in Excel with ease.

VLOOKUP Multiple Values overview


Download Practice Workbook

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


8 Methods to Return Multiple Values with VLOOKUP in Excel

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

Dataset Overview

We can use other Excel functions than VLOOKUP such as INDEX, SMALL, MATCH, ROW, COLUMNS, etc., suitably combining them to return multiple values. We can also use several Excel features like the Advanced Filter, the AutoFilter, and the Format as Table tool. So, without having any further discussion, let’s get into all 8 methods one by one.


1. Return Multiple Values with VLOOKUP Function

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 within the cell.

=B5&COUNTIF(B5:B$13,B5)

VLOOKUP Function

❸ After that, hit the ENTER button and drag the Fill Handle icon down to Autofill the Dept._Unique column.

VLOOKUP Multiple Values

❹ Then you will get the column filled.

COUNTIF Function

❺ Now select cell C16 and apply the formula

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

VLOOKUP Multiple Values

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

VLOOKUP Multiple Values

That’s it.

Read More: Excel VLOOKUP to Return Multiple Values in One Cell Separated by Comma


2. Pull Multiple Values Vertically

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 within the cell.

=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)))

❸ Then press the ENTER button.

Pull Values Vertically

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

Get Values Vertically

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

Read More: Excel VLOOKUP to Return Multiple Values Vertically


3. Extract Multiple Values Horizontally

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 within the cell.

=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)))

❸ Then press the ENTER button.

Applying Formula to Vlookup Multiple values

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

Extract Values Horizontally

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

Read More: VLOOKUP to Return Multiple Values Horizontally in Excel


4. Return Multiple Values 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.

Applying Formula

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

Values with Criteria

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

Read More: VLOOKUP Partial Match Multiple Values (3 Approaches)


5. Get Multiple Values 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 functions.

🔗 Steps:

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

❷ After that, type the formula within the cell.

=TEXTJOIN(",",TRUE,IF($B$5:$B$13=$C$15,$C$5:$C$13,""))

❸ Then press the ENTER button.

Multiple Values 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.

Read More: How to VLOOKUP Multiple Values in One Cell in Excel (2 Easy Methods)


6. Use of AutoFilter Feature

If you want to avoid using Excel formulas, then you can lookup 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.

Applying AutoFilter

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

Click Dropdown

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

❻ Then hit the OK button.

Filter Menu

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

Filtered Values


7. Using Advanced Filter Feature

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. We will be using to search throughout the data table to find out which employees work in 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.

Advanced Filter

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

❺ Now enter within the List range: bar

$B$4:$C$13

and then enter within the Criteria range: bar.

'Advanced Filter'!$E$4:$E$5

❻ After that, you can hit the OK button.

Command Box of Advanced Filter

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

Filtered Values

Read More: How to Vlookup and Return Multiple Values in Drop Down List


8. Formatting 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 Table option and select any of the table styles as you like.

Formatting table

❹ Then keep the My table has headers option marked in the Create Table dialog box and click OK.

Create Table dialog box

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

Created Filter

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

Menu for Filtering

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

Filtered Values


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 return multiple values with VLOOKUP 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 Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo