Easily lookup value and return multiple values in Excel

By default, if multiple values match the condition, the Excel VLOOKUP function can only return the first matched value. What if we want to get all the matched values? Don’t worry. The One-to-many Lookup feature of Kutools for Excel is your best choice. With this feature, you can easily lookup and return multiple matched values into a single cell, and even perform mathematical operations on the multiple matched values (such as count, sum, product, average, maximum, and minimum), without applying complicated formulas.

Lookup value and return multiple values - One to many lookup

Click Kutools > Super LOOKUP > One-to-many Lookup to enable the feature.

Lookup value and return multiple values - One to many lookup

As the below screenshot shows, you want to return all the corresponding names for each DEPT into each cell. Please do as follows.

1. Click Kutools > Super LOOKUP > One-to-many Lookup to enable the feature.

2. In the One-to-many Lookup (Separate the returned results with commas) dialog box, please do the below settings.

• (2.1) In the Output Range box, select the range of cells to output the results;
• (2.2) In the Lookup Values box, select the range which contains the values you want to look up;
• (2.3) In the Data Range box, select the whole table range which contains the max, min and the result columns;
• (2.4) In the Key Column drop-down list, select the lookup value column (here I select the DEPT column);
• (2.5) In the Return Column drop-down list, choose the result value column (here I select the Name column);
• (2.6) Click the OK button. See screenshot:

You can see multiple corresponding names for each DEPT are returned and separated with commas.

Notes:

1. If the selected data range does not include column headers, please uncheck the My data has headers box.
2. By default, the returned multiple results are separated with commas in the single cell. To handle the returned multiple results in a different way, please click Options, and choose one method you prefer from the Handle multiple results drop-down list.

   

   • The Separate the returned results with commas option, which is the default option, will separate the multiples results with commas.
   • The Separate the returned results with spaces option will separate the multiples results with space.
   • The Separate the returned results with newlines option will separate the multiples results with newlines.
   • The Separate the returned results with other delimiters option will separate the multiples results with the delimiter you specify. Just input the delimiter in the Separate the returned results with other delimiters textbox.
     
   • The Count the number of returned results option will return the counts of the multiple results, rather than the matched results themselves.
   • The Sum the returned results option will return the sum of the multiple results, rather than the matched results themselves.
   • The Calculate the product of the returned results option will return the production of the multiple results, rather than the matched results themselves.
   • The Calculate the average of the returned results option will return the average of the multiple results, rather than the matched results themselves.
   • The The maximum value of returned results option will return the maximum value of the multiple results.
   • The The minimum value of returned results option will return the minimum value of the multiple results.
3. You can always see the method of handling returned results on the header of the One-to-many Lookup dialog. When the function is run again, the method will remain the same as the last time it exited.

   

4. If the specific value you lookup doesn't exist, and you choose to separate the returned results with delimiters, an empty space, which stands for an #N/A error value, will be displayed. Or if the specific value you lookup doesn't exist and you choose to perform mathematical operations on the multiple matched values (such as count, sum, product, average, maximum, and minimum), 0 (zero), which also stands for an #N/A error value, will be returned.
5. To replace the returned empty space (#N/A error value) with a specified value, please click Options. Then check the Replace #N/A error value with a specified value box and enter certain value into the textbox.

   

   But the Replace #N/A error value with a specified value option doesn’t work when you want to replace the returned 0 (zero) (#N/A error value) with the value you specify.