how to use equal to and multiple Vlookup in single cell

I have created a multiselect dropdown for Cities in my sheet 1 and the postcodes associated with the dropdown is in sheet 2. This is how my sheet 2 looks.

1.) User is allowed to select multiple cities from the dropdown. As soon as user selects the city, I want to show in one cell the selected city and the associated postcodes. For e.g. If user selects Sion and Dadar from the dropdown then just below the dropdown user should be able to see something like this.

With the help of Vlookup i am able to retrieve either one of the value and also not able to show in a single cell with equals to sign.

2.) Also I have used VBA code from the internet to have multiple select and remove. The code works fine but I want to make some changes in it. Like when user selects two cities the value gets populated in the dropdown cell separated by "comma". I want everytime the second value to go on next line but to remain in the same cell and also dynamically adjust the row height with leaving some margin from top and bottom. I am new to VBA and don't know how exactly to get it on next line. This is how it currently looks.

But instead of above, I want it look like this

Here is the VBA code which i have used.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    If Target.Count > 1 Then Exit Sub
    On Error Resume Next
    Set xRng = Me.Range("J2, K2,L2,M2,N2")
    If xRng Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If Not Application.Intersect(Target, xRng) Is Nothing Then
        xValue2 = Target.Value
        xValue1 = Target.Value
        Target.Value = xValue2
        If xValue1 <> "" Then
            If xValue2 <> "" Then
                If InStr(1, xValue1, xValue2 & ",") > 0 Then
                    xValue1 = Replace(xValue1, xValue2 & ", ", "") ' If it's in the middle with comma
                    Target.Value = xValue1
                    GoTo jumpOut
                End If
                If InStr(1, xValue1, ", " & xValue2) > 0 Then
                    xValue1 = Replace(xValue1, ", " & xValue2, "") ' If it's at the end with a comma in front of it
                    Target.Value = xValue1
                    GoTo jumpOut
                End If
                If xValue1 = xValue2 Then        ' If it is the only item in string
                    xValue1 = ""
                    Target.Value = xValue1
                    GoTo jumpOut
                End If
                Target.Value = xValue1 & ", " & xValue2
            End If
        End If
    End If
    Application.EnableEvents = True
End Sub

How to use VLOOKUP with multiple conditions, Array formula in cell D17: The COUNTIF function counts values that equal a condition, however, it can to calculate multiple values in one cell. On the DataEntry sheet, select cell E6. On the Ribbon, click the Data tab, then click Data Validation.. From the Allow drop-down list, choose List. In the Source box, type an equal sign and INDIRECT function, referring to the first data cell in the Produce Type column: Click OK. Put code on Sheet Lookup

For Sion = 400022 You can use Vlookup formula

=VLOOKUP(Table1[Segments];Table1[Segments];1;FALSE)&" = "&VLOOKUP(Table1[Segments];Sheet2!A2:B4;2;FALSE)

I am not getting how to do it for multiselect. This works only when user select single option from the dropdown

VLOOKUP on Two or More Criteria Columns, This multiple condition summing function is designed to add up a column of numbers and where this column (argument 4) is equal to this value (argument 5), and One method is to use VLOOKUP and SUMIFS in a single formula. function with multiple conditions to return the RecordID to a cell, say A1. To add the additional column to the left, use the Excel VLOOKUP Formula: = D3 & “ “ & E3. for the first cell of the table and press Enter. Then drag it to the rest of the cells. Now calculate the maximum sales for East and West Zones separately. To calculate the maximum value, use the Excel VLOOKUP Formula: = MAX(D3:D14) for East zone

Another solution. Change Sheet name and ranges and try:

Option Explicit

Sub test()

    Dim strCitys As String
    Dim rng As Range
    Dim arr As Variant, strResults As Variant, City As Variant

    With ThisWorkbook.Worksheets("Sheet1")

        strCitys = .Range("A1").Value
        Set rng = .Range("D1:E3")
        strResults = ""

        If strCitys <> "" Then
            If InStr(1, strCitys, ",") = 0 Then

                strResults = Application.VLookup(strCitys, rng, 2, False)
                If Not IsError(strResults) Then
                    .Range("B1").Value = strCitys & "=" & strResults
                    .Range("B1").Value = strCitys & "=" & "Missing Code"
                End If


                For Each City In Split(strCitys, ",")

                    strResults = Application.VLookup(Trim(City), rng, 2, False)

                    If Not IsError(strResults) Then
                        If .Range("B1").Value = "" Then
                            .Range("B1").Value = Trim(City) & "=" & strResults
                            .Range("B1").Value = .Range("B1").Value & vbNewLine & Trim(City) & "=" & strResults
                        End If
                        If .Range("B1").Value = "" Then
                            .Range("B1").Value = Trim(City) & "=" & "Missing Code"
                            .Range("B1").Value = .Range("B1").Value & vbNewLine & Trim(City) & "=" & "Missing Code"
                        End If
                    End If

                Next City

            End If
            MsgBox "Please select city/ies."
        End If

    End With

End Sub


how to use equal to and multiple Vlookup in single cell, Select Formulas � Defined Names � Name Manager. Replace the Refers to: formula with the following formula: enter image description here� Deduce the first value by pressing together ”Ctrl + Shift + Enter” keys. Click the fill handle and drag down to get vlookup multiple values as you desire. Vlookup multiple values in one cell. You can follow the procedure in this section if you want to display the results of your vlookup in a single cell. Here is the array formula:

Master VLOOKUP Multiple Criteria and Advanced Formulas , How to Vlookup and return multiple values in Excel. The Excel VLOOKUP function is designed to return just one match. This requires a combined use of several functions such as INDEX, Either way, the number of cells in which you enter the formula should be equal to or larger� Vlookup value with multiple criteria with LOOKUP function. Supposing I have the following data range which I want to use two criteria to return the relative value, for example, I know the product and staff name need to return their corresponding total price value in the same row:

Advanced VLOOKUP in Excel with formula examples, For example, in cell C2, we have this in Excel using our tools. Vlookup Multiple Values Sample Worksheet the vba not equal operator. Vlookup to return multiple values in one cell with User Defined Function. The traditional VLOOKUP function may not help you to solve this problem, but, I will create a User Defined Function for you to deal with this task. Please do as follows: 1. Activate your worksheet which you want to vlookup multiple values into one cell. 2.

Vlookup multiple matches in Excel with one or more criteria, VLOOKUP with 2 criteria or more by using the INDEX and MATCH functions in use 'greater-than' and 'less-than', not just 'equals' in your comparison checks. Multiple Rows into One Cell or Extract a List of Values Filtered by Criteria with� Here are two examples where we write the VLOOKUP function a little differently. They're both using similar data sets but since we're pulling information from two separate columns, 3 and 2, we make that distinction at the end of the formula—the first one grabs the position of the person in A8 (Finley) while the second formula returns the name that matches the employee number in A9 (819868).

  • Try Target.Value = xValue2 & vbNewLine & xValue1
  • @MarcoGetrost With this it's coming on next line but remove option is not working. When i click on the same dropdown menu, its getting twice selected instead of removing
  • Could you please explain exact functioning of this above answer. I am new to excel and VBA also. Finding it difficult to understand
  • difficult in which place? You make dropdown which is filling from your extensible range. When you select value it appears on bottom of dropdown last value