VLOOKUP and Return All Matches in Excel (7 Ways)

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.


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.

VLOOKUP and Return Multiple Matches in a Column

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.

VLOOKUP and Return Multiple Matches in a Column

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.

VLOOKUP and Return Multiple Matches in a Column

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.

VLOOKUP and Return Multiple Matches in a Column

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

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.

VLOOKUP and Return All Matches in a Row in Excel

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.

VLOOKUP and Return All Matches in a Row in Excel

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.

VLOOKUP and Return All Matches in a Row in Excel

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.


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.

VLOOKUP to Return Multiple Values Based on Criteria

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.

VLOOKUP to Return Multiple Values Based on Criteria

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

VLOOKUP to Return Multiple Values Based on Criteria

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

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.

VLOOKUP and Draw Out All Matches with AutoFilter

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

VLOOKUP and Draw Out All Matches with AutoFilter

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

VLOOKUP and Draw Out All Matches with AutoFilter


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.

VLOOKUP to Extract All Matches with Advanced Filter in Excel

📌 Step 2:

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

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

➤ Press OK.

VLOOKUP to Extract All Matches with Advanced Filter in Excel

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

VLOOKUP to Extract All Matches with Advanced Filter in Excel


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.

VLOOKUP and Return All Values by Formatting as Table

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

VLOOKUP and Return All Values by Formatting as Table

📌 Step 2:

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

➤ Press OK and you’re done.

VLOOKUP and Return All Values by Formatting as Table

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

VLOOKUP and Return All Values by Formatting as Table


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.

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

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.


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 Drop Down List in Excel

VLOOKUP To Compare Two Lists (Same or Different Sheets)

How to Create Custom Sort List in Excel

Generate Random Number from List in Excel (4 Ways)

Excel Compare Two Lists and Return Differences (7 Ways)

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo