3 Ways to VLOOKUP Multiple Values in One Cell in Excel

In this lesson, I’ll demonstrate 3 effective approaches to VLOOKUP Multiple Values in One Cell in Excel. Moreover, these techniques will enable you to solve the problem that led you here. However, you will master several crucial Excel features and functions during this article, which will come in extremely handy for any assignment using Excel.

📁 Download Excel File

The sample worksheet that was used during the discussion is available to download for free right here.

Learn to Vlookup Multiple Values in One Cell in Excel with These 3 Methods

Here in this article, we will learn how to VLOOKUP Multiple Values in One Cell in Excel using different approaches. To be exact, I’ve provided a total of 3 methods down below.

This approach uses the combination of different functions along with the TEXTJOIN function as common and a core part to VLOOKUP multiple values in one cell. The TEXTJOIN function can be described below.

Here we will VLOOKUP multiple values in one cell using a combination of TEXTJOIN and IF functions. For instance, we have a dataset of salesman names, their products, and units sold at different times. We are going to find which products any specific salesman sold.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, copy down the following formula.

• Press Enter to apply the formula. As we can see here we have got duplicate results because the formula assigns these texts as different values.

• Copy down the formula for other results.

Here we will VLOOKUP multiple values in one cell using a combination of TEXTJOIN, IFERROR, MATCH, ROW, and  IF functions. For instance, we have a dataset of salesman names, their products, and units sold at different times. We are going to find which products any specific salesman sold without the duplicates.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, copy down the following formula.

• Press Enter to get the result.

• Copy down the formula along the column for other results.

In this section, we will be utilizing TEXTJOIN and UNIQUE functions to create a combination formula to VLOOKUP multiple values in one cell.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, copy the following formula

• Press Enter to get the answer. As you can see here we got the results without the duplicates.

• Copy down the formula along the column for all the results.

VBA is a special feature of Microsoft. It can be used as an internal programming language for Excel by which we can command Excel to do different types of operations with codes. Here we will demonstrate the solution to the above-stated problem with VBA code.

In this section, we will be using VBA code to VLOOKUP multiple values in one cell with duplicate results which means, the same texts will appear as different values.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, you have to enable the Developer tab of the ribbon. After that go to the Visual Basic option from the Developer Tab of the ribbon.

• A new window will pop-up and select Module from the Insert.

• Now type in the following code and save it.

Function ValueswithRep(input_range As Range, criteria_range As Variant, output_range As Range, Optional Separator As String = ",") As Variant
Dim OutString As String
On Error Resume Next
If input_range.Count <> output_range.Count Then
    ValueswithRep = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To input_range.Count
    If input_range.Cells(i).Value = criteria_range Then
        OutString = OutString & Separator & output_range.Cells(i).Value
    End If
Next i
If OutString <> "" Then
    OutString = VBA.Mid(OutString, VBA.Len(Separator) + 1)
End If
ValueswithRep = OutString
Exit Function
End Function

• Now as we have defined a new function let’s use this. Copy the following formula.

• Press Enter to get the results.

• As you can see we got duplicate results also. Copy down the formula along the column for the other results.

In this section, we will be using VBA code to VLOOKUP multiple values in one cell without duplicate results which means, the same texts will not appear.

⬇️⬇️ STEPS ⬇️⬇️

• Type in the following code for this condition in the Visual Basic window after inserting a new Module and save it.

Function ValueswithoutRep(target_range As String, input_range As Range, CNumber As Integer)
Dim ix1 As Long
Dim OutputString As String
For ix1 = 1 To input_range.Columns(1).Cells.Count
  If input_range.Cells(ix1, 1) = target_range Then
    For Jx1 = 1 To ix1 - 1
    If input_range.Cells(Jx1, 1) = target_range Then
      If input_range.Cells(Jx1, CNumber) = input_range.Cells(ix1, CNumber) Then
        GoTo Skip
      End If
    End If
    Next Jx1
    OutputString = OutputString & " " & input_range.Cells(ix1, CNumber) & ","
Skip:
  End If
Next ix1
ValueswithoutRep = Left(OutputString, Len(OutputString) - 1)
End Function

• Now as we have defined another function, let’s use this to solve our problem. Copy the following formula.

• After that Press Enter to get the results.

• As you can see the duplicate texts are merged there is only one Laptop here in the results. Copy down the formula along the column for the total result.

📄 Important Notes

🖊️  Be careful while using the code as there are a lot of terms and if you lose concentration the code may be incorrect.

🖊️  Use shortcuts to reduce time, use F4 for absolute value.

🖊️  While writing formulas carefully see the suggestions and don’t forget the commas and parentheses.

🖊️  Be careful while defining criteria to be accurate as the dataset.

🖊️  If you download a macro-enabled Excel file don’t forget to unblock the file from the properties menu.

📝 Takeaways from This Article

📌  Use of IFERROR, ROW, MATCH, IF, TEXTJOIN, and UNIQUE functions.

📌  How to VLOOKUP multiple values in one cell both with or without duplicate results.

📌  Creating formulas using multiple functions.

📌  Using VBA codes to customize functions.

📌  How to define a new function.

Conclusion

Firstly, I hope you were able to use the techniques I demonstrated in this VLOOKUP Multiple Values in One Cell In Excel lesson. As you can see, there are a lot of options on how to do this. However, decide deliberately on the approach that best addresses your circumstance. Most importantly I advise repeating the steps if you become confused in any of the steps if you get stuck. Practice on your own after taking a look at the Excel file in the practice workbook as I’ve provided above because practice makes a man perfect. Above all, I earnestly hope you can use it. Please share your experience with us in the comment box about how your experience was throughout the article and what we need to improve or add to make things better for you folks. However, if you have any problem regarding Excel spit it in the comment box. The Excelden crew is always available to answer your questions. In conclusion, keep educating yourself by reading. For more Excel-related articles please visit our website Excelden.com.