**VLOOKUP** is a useful function to look for the values in the dataset and return the necessary part of that dataset. If you are curious to know how you can use **VLOOKUP** **to return multiple values** in the drop-down list, then this article may come in handy for you. In this article, we discuss how you can use the **VLOOKUP** function to return multiple values with a drop-down list with elaborate explanations.

**Table of Contents**hide

## Download Practice Workbook

Download this practice workbook below.

## 2 Easy Ways to Vlookup and Return Multiple Values in Drop Down List

We are going to present two methods of how you can use the drop-down with the conjunction of the **VLOOKUP **function to return multiple values. A vast array of formulas is going to be used here. For avoiding any version or compatibility issues, try to use the **Excel 365** version.

### 1. Vlookup Multiple Values Combining INDEX, MATCH & ROW Functions

Using the combination of **VLOOKUP**, **INDEX**, **MATCH,** **SMALL** **IF****, ****IFERROR**, and **ROW**, we can form a formula that can help us to return multiple values from the dataset according to the values stored in a drop-down list.

**Steps**

- We have the sales record of each salesperson.
- We now want to get the sales value of each person.
- Each person’s name would be stored in a dropdown menu.

- In order to create a drop-down list, we first need to copy the salesman’s name to the right side of the sheet.
- In the range of cells
**E5:E12**.

- Then select the range of cell
**E5:E12**and then go to**Data**tab >**Data Tools**group >**Remove duplicate**.

- Then a warning window like the below image will pop up.
- In that message box, select
**Continue with the current selection**. - Then click on
**Remove Duplicates**.

- In the next
**Remove Duplicate**dialog box, make sure that**Column E**is checked. - Click
**OK**after this.

- Then arrange the cell as shown below.
- And then select cell
**F4**and then go to**Data**tab >**Data tools**>**Data Validation**.

- In the next dialog box, go to the
**Settings**tab. - Then from the first dropdown list, select
**List**. - Select the range of cell
**E9:E12**as the source of the dropdown list. - Click
**OK**after this.

- After clicking
**OK**, we will notice that the drop-down list is now ready with all the salesman names. - Then select the cell
**F5**and enter the following formula:

`=IFERROR(INDEX($C$5:$C$12, SMALL(IF($F$4=$B$5:$B$12, MATCH(ROW($B$5:$B$12), ROW($B$5:$B$12)), ""), ROW(B1))), "")`

Entering this formula will get the sales value of the **Simon**.

**Formula Breakdown**

**ROW($B$5:$B$12)**

⮚ This part of the formula will return the row number of the range of cells in array format.

**MATCH(ROW($B$5:$B$12), ROW($B$5:$B$12))**

⮚ This portion of the formula will match the location of the row array values in the output of the **ROW** output. The output, in this case, is supposed to be 1,2,3,4,5,6,7,8. Which indicates the serial of the matched value.

**IF($F$4=$B$5:$B$12, MATCH(ROW($B$5:$B$12), ROW($B$5:$B$12)), “”)**

⮚ The **IF** function here actually enters a loop. Here, if, any of the values of the range of cell **B5:B13** is equal to the cell value **F4**, then the next portion of the formula, which is the **MATCH** function, will be executed. After this, the matched cells serial in the range of cell **B5:B13** will be determined through the **MATCH** function. In the given example, we got Simon in cell **F4**. Now if the function will search for the **Simon **in the **B5:B13 **range.** Simon** has been found in cell **B5**. From the **MATCH** function, this **B5** is in the 1st and 6th serial in the range of cells. So the return value will be 1 and 6.

**SMALL(IF($F$4=$B$5:$B$12, MATCH(ROW($B$5:$B$12), ROW($B$5:$B$12)), “”), ROW(B1))**

⮚ The **SMALL** function will get the nth smallest value of the output in the previous section of the formula. Here the output of the **ROW(B1)** is 1. So, this part of the formula will return the 1st smallest value of the 1 and 6. So the return will be 1.

**INDEX($C$5:$C$12, SMALL(IF($F$4=$B$5:$B$12, MATCH(ROW($B$5:$B$12), ROW($B$5:$B$12)), “”), ROW(B1)))**

⮚ This penultimate part of the formula will extract the cell value according to the serial mentioned in the previous part of the formula.

**IFERROR(INDEX($C$5:$C$12, SMALL(IF($F$4=$B$5:$B$12, MATCH(ROW($B$5:$B$12), ROW($B$5:$B$12)), “”), ROW(B1))), “”)**

⮚ This final part of the code will avoid any kind of error value by placing “”(space) in the place of any error.

- Then drag the fill handle to cell
**F6**. - We get all of the values related to
**Simon**.

**Read More:** **VLOOKUP to Return Multiple Values Horizontally in Excel**

### 2. Return Multiple Values Using VLOOKUP Function

In this procedure, we are going to use the combination of the **VLOOKUP** and **IFERROR** functions to return multiple values according to values stored in a dropdown list.

**Steps**

- To begin with, we need to create the below table and change its name to
**sales_record**from the**Table Design**tab.

- In order to create a drop-down list, we first need to copy the salesman’s name to the right side of the sheet.
- In the range of cells
**E5:E12**.

- Then select the range of cell
**E5:E12**and then go to**Data**tab >**Data Tools**group >**Remove duplicate**.

- In the next
**Remove Duplicate**dialog box, make sure that**Column E**is checked. - Click
**OK**after this.

- Then arrange the cell as shown below.
- And then select cell
**F4**and then go to**Data**tab >**Data tools**>**Data Validation.**

- In the next dialog box, go to the
**Settings**tab. - Then from the first dropdown list, select
**List**. - Select the range of cell
**E9:E12**as the source of the dropdown list. - Click
**OK**after this.

- Now we got the drop-down list of the salesman’s name.

Then select the cell **H5** and enter the following formula:

`=IFERROR(VLOOKUP(G5,sales_record,2,0),"")`

**Formula Breakdown**

**VLOOKUP(G5,sales_record,,0)**

**⮚ VLOOKUP **function will look for the location of the value of cell **G5 **in the **Salesman** column**,** if it finds the value, then it will return the cell value in the 2-column offset, in the same row.

**IFERROR(VLOOKUP(G5,sales_record,2,0),””)**

⮚ This final part of the code will avoid any kind of error value by placing **“”**(space) in the place of any error.

Then select the cell** I5** and enter the following formula:

`<code>`

**=IFERROR(VLOOKUP(G5,sales_record,3,0),””)**

**Formula Breakdown**

**VLOOKUP(G5,sales_record,3,0)**

**⮚ VLOOKUP **function will look for the location of the value of cell **G5 **in the **Salesman** column, if it finds the value, then it will return the cell value in the 4-column offset, in the same row.

**IFERROR(VLOOKUP(G5,sales_record,3,0),””)**

⮚ This final part of the code will avoid any kind of error value by placing **“”**(space) in the place of any error.

Then select the cell **J5** and enter the following formula:

`=IFERROR(VLOOKUP(G5,sales_record,4,0),"")`

**Formula Breakdown**

**VLOOKUP(G5,sales_record,4,0)**

**⮚ VLOOKUP **function will look for the location of the value of cell **G5 **in the **Salesman** column, if it finds the value, then it will return the cell value in the 4-column offset, in the same row.

**IFERROR(VLOOKUP(G5,sales_record,4,0),””)**

⮚ This final part of the code will avoid any kind of error value by placing **“”**(space) in the place of any error.

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

## Can VLOOKUP Function Return Multiple Values?

Yes, **the VLOOKUP function** can return multiple values in Excel if your lookup value contains a range. Here the whole procedure is demonstrated with the sales record for each salesman in each month.

**Steps**

- Here, we can have the sales record of different salesmen in the dataset below.
- At the same time, we want to get the sales record of multiple salesmen at the same time.

- To do this, select the cell
**H5**and enter the following formula:

`=VLOOKUP(G5:G8,B5:E13,2,0)`

- Immediately after entering the formula, we can observe that all of the salesman sales records for the month of
**January**sales getting out in the worksheet.

**Note**

Here, the output is an array, not a single cell value. Which made it possible to return multiple values in a single formula.

And also, you must have a lookup range in place of lookup_value in the **VLOOKUP **function.

- To get the sales record for
**March Sales**, select the cell**I5**and enter the following formula:

`=VLOOKUP(G5:G8,B5:E13,4,0)`

- Immediately after entering the formula, we can observe that all of the salesman sales records for the month of
**March Sales**getting out in the worksheet.

## How to Use VLOOKUP Function for Multiple Drop Down List in Excel

In the previous example, we have seen that the **VLOOKUP** enables the user to use the dropdown menu. In this example, we will use the **VLOOKUP** and **MATCH** functions with multiple dropdown list.

**Steps**

- We have the salesman record for each month in the below dataset
- In this dataset, we are going to use two separate dropdown menus using which you can filter out your preferred value for specified salesperson and month.

- To create the first dropdown list, select cell
**H4**and go to the**Data**tab >**Data tools**>**Data Validation**.

- In the next dialog box, go to the
**Settings**tab. - Then from the first dropdown list, select
**List**. - Select the range of cell
**B5:B13**as the source of the dropdown list. - Click
**OK**after this.

- We can observe that the dropdown menu is here now.
- To add the second dropdown menu, select cell
**H5**and then go to the**Data**tab >**Data tools**>**Data Validation**.

- In the next dialog box, go to the
**Settings**tab. - Then from the first dropdown list, select
**List**. - Select the range of cell
**C4:E4**as the source of the dropdown list. - Click
**OK**after this.

- Then select the cell
**H6**and enter the following formula:

`=VLOOKUP(H4,B5:E13,MATCH(H5,B4:E4,0),FALSE)`

**Formula Breakdown**

**MATCH(H5,B4:E4,0)**

**⮚ **This part of the formula will get the serial value in cell **H5** in the range of cell **B4:E4**. In this case, the output will be 2.

**VLOOKUP(H4,B5:E13,MATCH(H5,B4:E4,0),FALSE)**

**⮚ **The final part of the formula will look for the value of cell **H4 **in the range of cell **B5:E13**, and after it found the location, it returns the value in the cell offset to the number returned in the **MATCH **function, which is 2.

## Conclusion

To sum it up, how you can vlookup to return multiple values with a dropdown list is answered here by 2 different examples. We also answered if we can return multiple values through **VLOOKUP** at all and how we can use multiple drop-down lists.

For this problem, a workbook is available to download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the **ExcelDemy** community will be highly appreciable.

## Related Articles

**Excel VLOOKUP to Return Multiple Values in One Cell Separated by Comma****How to VLOOKUP Multiple Values in One Cell in Excel (2 Easy Methods)****Excel VLOOKUP Function to Return Min Value from Multiple Hits****VLOOKUP Partial Match Multiple Values (3 Approaches)****How to Perform VLOOKUP with Multiple Rows in Excel (5 Methods)****VLOOKUP Max of Multiple Values (With Alternative)**