How To Vlookup To Return Multiple Values In One Cell In Excel?

Question

So I have the code that pastes multiple values into one cell but I was hoping to be able to put a semicolon in between the cell's value.

This code allows a vlookup to find multiple cell values and output them in one cell.

Function MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long)
'Update 20150310
Dim rng As Range
Dim xResult As String
xResult = ""
For Each rng In pWorkRng
    If rng = pValue Then
        xResult = xResult & ";" & rng.Offset(0, pIndex - 1)
    End If
Next
MYVLOOKUP = xResult
End Function

When I do this it does put a semicolon in between the values like I want, but also has a billion semicolons after.

Can you put Debug.Print rng.Offset(0, pIndex - 1) before xResult = xResult & ";" & rng.Offset(0, pIndex - 1) and see what that's returning? — dwirony
– dwirony, Commented Jan 22, 2019 at 20:56
If you have Office 365: =TEXTJOIN(";",TRUE,IF(A1:A1000="MyValue",D1:D1000,"")) As an Array Formula with Ctrl-Shift-Enter — Scott Craner
– Scott Craner, Commented Jan 22, 2019 at 21:00
it is a formula that you add on the sheet changing the ranges to the desired input and output, it is not vba — Scott Craner
– Scott Craner, Commented Jan 22, 2019 at 21:03

Mathieu Guindon · Accepted Answer · 2019-01-22 21:01:44Z

1

I'd work with an array for this. Start by sizing it to fit the entire source:

Dim results As Variant
ReDim results(1 To pWorkRng.Count)

Then maintain a counter for the index of the last item in that array, and write at that index:

Dim currentIndex As Long

For Each rng In pWorkRng
    If Not IsError(rng.Value) Then
        If rng.Value = pValue Then
            currentIndex = currentIndex + 1
            results(currentIndex) = rng.Offset(0, pIndex - 1)
        End If
    End If
Next

When the loop completes, you'll have all the results up to currentIndex, and then a bunch of Empty values; truncate the array with ReDim Preserve:

ReDim Preserve results(1 To currentIndex)

And now you can return a string with all the results, using String.Join:

MYVLOOKUP = String.Join(results, ";")

answered Jan 22, 2019 at 21:01

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

IAmNerd2000 · Accepted Answer · 2019-01-23 05:07:37Z

0

If Mathieu Guindons method doesn't work then, try to add the following to your code after the following line:

xResult = xResult & ";" & rng.Offset(0, pIndex - 1)

    Do While (InStr(xResult, ";;") > 0)
        xResult = Replace(xResult, ";;", ";")
    Loop

answered Jan 23, 2019 at 5:07

IAmNerd2000

7711 gold badge4 silver badges12 bronze badges

Collectives™ on Stack Overflow

How To Vlookup To Return Multiple Values In One Cell In Excel?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related