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.

**Table of Contents**hide

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

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

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

❹ Then you will get the column filled.

❺ Now select cell **C16** and apply the formula

`=VLOOKUP(B16,E5:F13,2,0)`

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

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.

❹ 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**.

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

❹ 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.**

**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)),"")`

❸ Then press the **ENTER** button.

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

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

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.

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

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

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

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

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

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

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.

## VLOOKUP Multiple Values: Knowledge Hub

**VLOOKUP Multiple Values in One Cell****VLOOKUP to Return Multiple Values in One Cell Separated by Comma****Vlookup and Return Multiple Values in Drop Down List**

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