Last active
April 5, 2021 15:56
-
-
Save ashleycam3ron/83be8f29bc01c2b9017535883d264023 to your computer and use it in GitHub Desktop.
Excel vlookup to return multiple values as comma separated list in one cell
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Option Explicit | |
Function LookupCSVResults(lookupValue As Variant, lookupRange As Range, resultsRange As Range) As String | |
Dim s As String 'Results placeholder | |
Dim sTmp As String 'Cell value placeholder | |
Dim r As Long 'Row | |
Dim c As Long 'Column | |
Const strDelimiter = "|||" 'Makes InStr more robust | |
s = strDelimiter | |
For r = 1 To lookupRange.Rows.Count | |
For c = 1 To lookupRange.Columns.Count | |
If lookupRange.Cells(r, c).Value = lookupValue Then | |
'I know it's weird to use offset but it works even if the two ranges | |
'are of different sizes and it's the same way that SUMIF works | |
sTmp = resultsRange.Offset(r - 1, c - 1).Cells(1, 1).Value | |
If InStr(1, s, strDelimiter & sTmp & strDelimiter) = 0 Then | |
s = s & sTmp & strDelimiter | |
End If | |
End If | |
Next | |
Next | |
'Now make it look like CSV | |
s = Replace(s, strDelimiter, ",") | |
If Left(s, 1) = "," Then s = Mid(s, 2) | |
If Right(s, 1) = "," Then s = Left(s, Len(s) - 1) | |
LookupCSVResults = s 'Return the function | |
End Function |
Thanks to Engineer Toast:
https://superuser.com/questions/536234/excel-how-to-vlookup-to-return-multiple-values#answer-881414
@ ashleycam3ron Thanks for sharing this! Helped a lot!
That's so great !!!! Bravo Ashley
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Thanks to Engineer Toast:
https://superuser.com/questions/536234/excel-how-to-vlookup-to-return-multiple-values#answer-881414