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.

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.

❸ 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

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

❸ Then press the ENTER button.

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

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.

❸ Then press the ENTER button.

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

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

❸ Then press the ENTER button.

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

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.

❸ Then press the ENTER button.

That’s it.

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

and then enter within the 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:

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.

Related Articles

• Excel VLOOKUP Function to Return Min Value from Multiple Hits
• How to Perform VLOOKUP with Multiple Rows in Excel (5 Methods)
• VLOOKUP Max of Multiple Values (With Alternative)