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.

**Table of Contents**hide

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

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

❸ After that, hit the **ENTER** button.

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

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

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

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

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

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

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

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

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

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

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

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

**List range:**bar and

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

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

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

**VLOOKUP with Drop Down List in Excel****VLOOKUP To Compare Two Lists (Same or Different Sheets)****How to VLOOKUP from Multiple Columns with Only One Return in Excel (2 Ways)****VLOOKUP to Return Multiple Columns in Excel (4 Examples)****How to Return the Highest Value Using VLOOKUP in Excel****VLOOKUP with Numbers in Excel (4 Examples)****VLOOKUP Formula to Compare Two Columns in Different Sheets!****10 Best Practices with VLOOKUP in Excel**