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.
How to Vlookup and Return Multiple Values in Drop Down List: 2 Easy Ways
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 Microsoft 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: How to Use VLOOKUP Function on Multiple Rows 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 have 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:
=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: How to Use 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 records 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.
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 lists.
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 finds the location, it returns the value in the cell offset to the number returned in the MATCH function, which is 2.
Download Practice Workbook
Download this practice workbook below.
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.
Related Articles
- Excel VLOOKUP to Return Multiple Values in One Cell Separated by Comma
- How to VLOOKUP Multiple Values in One Cell in Excel
- Excel VLOOKUP Function to Return Min Value from Multiple Hits
- VLOOKUP to Return Multiple Values Horizontally in Excel
- Find Max of Multiple Values by Using VLOOKUP Function in Excel
<< Go Back to VLOOKUP Multiple Values | Excel VLOOKUP Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
