There are several ways to apply **VLOOKUP **or vertical lookup in Microsoft Excel to return data based on multiple matches. In this article, you’ll get to know all possible approaches to activate **VLOOKUP **and extract all available data with proper illustrations.

**Table of Contents**hide

**Download Practice Workbook**

You can download the Excel workbook that we’ve used to prepare this article.

**7 Ways to VLOOKUP and Return All Matches in Excel**

The **VLOOKUP** function looks for a value in the leftmost column of a table and returns a value in the same row from the specified column. But this function is unable to extract data based on more than one match from a column. So, we have to insert some other functions and formulas to look up a value and return all matches found in a column.

**1. VLOOKUP and Return Multiple Matches in a Column**

In the following picture, we have a table containing random names of several employees and their departments. Assuming that we want to show the names of the employees in a single column who are working in the Manufacture department.

If you’re an **Excel 365** user, then you can go for the **FILTER** function here to find the return values in a moment. With the **FILTER **function, the required formula in the output **Cell C16** will be:

`=FILTER(C5:C13,C15=B5:B13)`

After pressing **Enter**, you’ll see the names of the employees from the **Manufacture **department in a vertical array.

Or if you’re using an **older version** of Microsoft Excel then you have to use the following combined formula:

`=INDEX($C$5:$C$13, SMALL(IF(($C$15=$B$5:$B$13), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), ""),ROWS($A$1:A1)))`

After pressing **Enter**, you’ll find the first name of the employee in the output **Cell C16**.

By using **Fill Handle** from **Cell C16** to downward, you’ll get the rest of the names of the employees from the specified department at once.

**🔎**** How Does This Formula Work?**

**ROW($B$5:$B$13):**The**ROW**function extracts the row numbers of the defined cell references and returns the following array:

**{5;6;7;8;9;10;11;12;13}**

**MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)):****MATCH**function here converts the extracted row numbers starting from 1. So, this part of the formula returns an array of:

**{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)), “”):**With the help of**IF**function, this part of the formula returns the index number of the rows that meet the specified condition. So, this part returns an array of:

**{“”;2;””;4;””;””;””;””;9}**

- The
**SMALL**function in the formula pulls out the first small number found in the previous step and assigns this number to the second argument**(row_number)**of the**INDEX**function. - Finally, the
**INDEX**function shows the name of the employee based on the specified row number. - The
**ROWS**function in this formula defines the**k-th**number for the**SMALL**function. While using**Fill Handle**to fill down the rest of the cells, the formula uses this**k-th**number to extract data followed by the**SMALL**function.

**Read More:** **INDEX MATCH vs VLOOKUP Function (9 Examples)**

**2. VLOOKUP and Return All Matches in a Row in Excel**

If you want to see the names of the employees horizontally then you have to combine the **FILTER **function with the **TRANSPOSE **function. The **TRANSPOSE **function converts a vertical range of cells to a horizontal range or vice versa. And to use this combined formula, you must be an **Excel 365** user.

So, the required formula in the output **Cell C16** will be:

`=TRANSPOSE(FILTER(C5:C13,C15=B5:B13))`

Now press **Enter **and you’ll be shown the names of the employees from the Manufacture department in a horizontal array.

Or insert the following formula in the output **Cell C16** if you’re not an Excel 365 user.

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

Press **Enter **and you’ll find the first name of the employee from the specified department.

Now, use **Fill Handle** and drag the **Cell C16** rightward until you find the first **#NUM** error. And you’ll get all the names from the **Manufacture **department horizontally.

The formula inserted here is almost similar to the first lengthy formula used in the previous example of the article where the extracted data had to be displayed vertically. The only major difference is that we’re using the **COLUMNS** function here to specify the sequence number of the **SMALL **function. While auto-filling the cells horizontally, the formula will follow the sequence number of the **SMALL **function to extract data.

**Read More:** **Excel VLOOKUP to Return Multiple Values Vertically**

**3. VLOOKUP to Return Multiple Values Based on Criteria**

We’ve added an extra column in the middle of the table. This column stores the project IDs that are assigned to the corresponding employees present in **Column D**. So, we’ll input two different conditions now and extract data based on all matches found.

For example, we want to know the names of the employees who are currently working in the **Sales **department on the project ID of **DMR 103**.

The required formula in the output **Cell C17** will be:

`=IFERROR(INDEX($D$5:$D$13, SMALL(IF(1=((--($C$15=$B$5:$B$13)) * (--($C$16=$C$5:$C$13))), ROW($D$5:$D$13)-4,""), ROW()-16)),"")`

Press **Enter **and you’ll find the first name of the employee under the specified criteria.

Now fill down the **Cell C17** to show the rest of the name with the given conditions.

**🚩**** Some Important Features of this Formula:**

- This formula is also quite similar to the one used in the previous method.
- In this formula, the
**IFERROR**function has been used to show a customized output if any error is found. - The
**IF**function in this formula combines two different criteria and with the help of double-unary, the boolean values**(TRUE or FALSE)**turn into**1 or 0**. The function then returns the index number of the rows that have matched with the given criteria. **ROW($D$5:$D$13)-4:**In this part, the number**‘4’**is the row number of the Employee header.**ROW()-16:**And the numerical value**‘16’**used in this part denotes the previous row number of the first output cell.

**Read More:** **Use VLOOKUP with Multiple Criteria in Excel (6 Methods + Alternatives)**

**4. VLOOKUP and Draw Out All Matches with AutoFilter**

By using **AutoFilter**, we can extract data based on all matches more easily. Since we’re going to pull out the names of the employees from the Manufacture department, we have to go after the following steps:

**📌**** Step 1:**

➤ Select the entire data table and right-click the mouse.

➤ Choose the **‘Filter by Selected Cell’s Value’** option from the **Filter **options.

So, you’ve just activated the **Filter **buttons for your headers.

**📌**** Step 2:**

➤ Click on the **Filter **button from the **Department **header.

➤ Put a mark on the **Manufacture **option only.

➤ Press **OK **and you’re done.

Like in the screenshot below, you’ll be displayed the resultant and filtered data.

**Read More:** **VLOOKUP with Multiple Matches in Excel**

**Similar Readings**

**How to Apply Double VLOOKUP in Excel (4 Quick Ways)****VLOOKUP Not Working (8 Reasons & Solutions)****How to Combine Excel SUMIF & VLOOKUP Across Multiple Sheets****VLOOKUP to Return Multiple Columns in Excel (4 Examples)****How to VLOOKUP with Multiple Conditions in Excel (2 Methods)**

**5. VLOOKUP to Extract All Matches with Advanced Filter in Excel**

You can also use the **Advanced Filter** where you have to define the criteria by selecting the criteria range from your Excel spreadsheet. In the following picture, **B15:B16** is the criteria range.

**📌**** Step 1:**

➤ Select the entire data table.

➤ Under the **Data **ribbon, click on the **Advanced **command from the **Sort and Filter** drop-down.

A dialogue box named **Advanced Filter** will open up.

**📌**** Step 2:**

➤ Select the entire data table for the **List Range** input.

➤ Choose **B15:B16** for the input of the **Criteria Range**.

➤ Press **OK**.

And you’ll be displayed the filtered result with the names of the employees from the **Manufacture **department only.

**Read More:** **VLOOKUP with Multiple Criteria in Excel (6 Examples)**

**6. VLOOKUP and Return All Values by Formatting as Table**

Now we’ll show you another simple method to filter the data table by converting it into a formatted table.

**📌**** Step 1:**

➤ Select the primary data table first.

➤ From the **Format as Table** drop-down under the **Home **tab, choose any of the tables you prefer.

After the first step, your data table will now look like as shown in the following screenshot with the filtered headers.

**📌**** Step 2:**

➤ Select the **Manufacture **option after clicking on the filter button from the **Department **header.

➤ Press **OK **and you’re done.

The screenshot below is showing the outputs based on the specified selection.

**Read More:** **Use of VBA VLOOKUP to Find Values from Another Worksheet in Excel**

**7. VLOOKUP to Pull Out All Matches into a Single Cell in Excel**

The **TEXTJOIN **function concatenates a list or range of text strings using a delimiter. By incorporating **TEXTJOIN **and **IF **functions together, we can look up a value and extract data based on all matches into a single cell.

The required formula in the output **Cell C16** will be:

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

After pressing **Enter**, you’ll find the employee names from the **Manufacture **department in a single cell separated by commas.

In this formula, the **IF **function returns the array with the matched names as well as the boolean value ‘FALSE” for non-matched cells. The **TEXTJOIN **function then joins all the names found with the specified delimiter.

**Read More:** **VLOOKUP Partial Text from a Single Cell in Excel**

**Concluding Words**

I hope all these methods mentioned above will now help you to apply them in your Excel spreadsheets when necessary. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

**You May Also Like to Explore**

**VLOOKUP with Numbers in Excel (4 Examples)****How to Use VLOOKUP for Rows in Excel (With Alternatives)****VLOOKUP with Drop Down List in Excel****How to VLOOKUP from Multiple Columns with Only One Return in Excel (2 Ways)****VLOOKUP to Search Text in Excel (4 Easy Ways)****How to Pull Data from Multiple Worksheets in Excel (4 Quick Ways)****VLOOKUP to Return Multiple Values Horizontally in Excel**

Very neat.

It saves loads of work.

Much appreciated, Nehad.

Cheers 🙂

Thanks for your feedback, Victor!

I mean.. this is a contribution to society. Thanx

Thanks for this article! Today I used the #7. VLOOKUP to Pull Out All Matches into a Single Cell in Excel, and it worked like a charm! I’ve bookmarked this page so I can return and learn the other options.

Glad to know it helped in your work!

Trying #7 but keep getting #VALUE! error, can someone help?

Hello JULIO!

Hope you are doing well. In our dataset, Method 7 is working properly without any errors. If you are facing errors, that can be for the following reasons:

1. If any cells that are used in the

TEXTJOINfunction exceed252 characters.2. If the output of the

TEXTJOINfunction exceeds 32672 characters which is the cell limit in Excel.3. And, sometimes Excel fails to identify the delimiter as text. For this, you should use delimiters inside the

inverted commasand shouldn’t use theCHAR functionsto generate any symbols.I hope, your problem will be solved in this way. You can share more problems in an email at

[email protected]