How to Vlookup and Return Multiple Values in Drop Down List

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.


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.

Vlookup return Multiple Values in drop down list Combining INDEX, MATCH & ROW Functions

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

copy values to return multiple values in drop down list using vlookup function

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

using vlookup function to remove duplicates to return multiple values in drop down list

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

drop down list addition by data validation to return multiple values with drop down list using VLOOKUP funtction

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

VLOOKUP Return Multiple Values Using VLOOKUP Function

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

copy and paste salesman value to return multiple value

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

remove duplicates to return multiple values using vlookup function with drop down list

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

returning multiple values by using vlookup function with drop down list

  • 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),"")

multiple values return using vlookup function with drop down list

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.

VLOOKUP FUNCTION to use multiple drop down list

  • 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

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo