Software Engineering Blog

What am i after in these days

Excel Tip: Lookup and return multiple values in one cell

During another fun day of excel filling for program usages of Mainframe systems , i had program list ,which i fed into a jenkins search and got where they were used. Many programs were used in many modules and I wanted to show one program’s usages in single cell. So i google it. There were two options according to your excel version:

1- For newer Excel versions ; you should use textjoin , unique and if functions combined. A column is for lookup , b for value.

=TEXTJOIN(", ",TRUE,UNIQUE(IF(D2=$A$2:$A$20,$B$2:$B$20,"")))

2- For older, poorer excel versions : You should be writing a VBA function. Unfortunately that was what i had to do. Put Alt + F11 and insert a module.

'Code by Sumit Bansal (
Function SingleCellExtract(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
  Dim i As Long
  Dim Result As String
  For i = 1 To LookupRange.Columns(1).Cells.Count
  If LookupRange.Cells(i, 1) = Lookupvalue Then
  Result = Result & " " & LookupRange.Cells(i, ColumnNumber) & vbNewLine
  End If
  Next i
  SingleCellExtract = Trim(Trim(Left(Result, Len(Result) )))
  End Function

Then you should call in the cell =SingleCellExtract and give parameters.

Do not give unlimited range because that really hurts performance.

Format your cells for WRAP TEXT option otherwise newlines are omitted.

For more goto my reference:

Please follow and like us:






Leave a Reply

Your email address will not be published. Required fields are marked *