In one in my projects I'm in need to apply VLOOKUP to several lines of a cell and displaying the output of theses VLOOKUPs in another cell.

I'm seeking a function like that:

Function LookupNewlineSeparatedItems(lookupRange as Range, cell as Cell)
End Function

Assume that loopupRange contains something like:

and cell contains the newline separated lines

the function should display the result:

(There is no such thing like A5 in the list, so a special value should displayed.)

I have no experience in VBA Excel up to now. How can I develop a working solution for this?

CodePudding user response：

An additional parameter called column_index added for selecting the column from provided range. This provides slight additional flexibility.

Function LookupNewlineSeparatedItems(range As range, column_index As Integer, cell As Variant) As String
 
   Dim values As Variant
      
   'split cell values based on new line character into an array
   
   values = Split(cell, vbNewLine)
         
  'Loop over the array doing a vlookup on each value upon the given range
  
    For i = 0 To UBound(values)
    
       Dim lookup As Variant
       lookup = Application.VLookup(values(i), range, column_index, 0)
       
       'if cell value not found then substitute with "NA"
       
       If Application.IsNA(lookup) Then
         values(i) = "NA"
       Else
        values(i) = lookup
       End If
       
    Next
    
    'use VBA Join function to get final output
    LookupNewlineSeparatedItems = Join(values, vbLf)

End Function

CodePudding user response：

Column B alignment needs to have wrap text selected.

Function LookupNewLineSeparatedItems(lookupRange As Range, cell As Range)

   Dim ar, v, i As Long
   ar = Split(cell.Value2, vbLf)
   For i = 0 To UBound(ar)
      v = Application.VLookup(ar(i), lookupRange, 2, 0)
      If IsError(v) Then v = "N/A"
      ar(i) = v
   Next
   LookupNewLineSeparatedItems = Join(ar, vbCrLf)
 
End Function

CodePudding user response：

Please, try the next function (without any iteration):

Function LookupNewlineSeparatedItems(lookupRange As Range, cel As Range)
  Dim arrV, arrSpl: arrSpl = Split(cel.value, vbLf)
  With Application
    arrV = .IfError(WorksheetFunction.VLookup(arrSpl, lookupRange, 2, False), "N/A")
    LookupNewlineSeparatedItems = Join(.Transpose(.Transpose(arrV)), vbLf)
  End With
End Function

It can be tested in the next way:

Debug.Print LookupNewlineSeparatedItems(Range("A2:B5"), ActiveCell)

The above code line supposes that in A1:A2 there are headers and ActiveCell is the one keeping the new line separated strings (obtained by pressing Alt Enter in cell edit mode).

It also assumes that "A1", "A2", "A3", "A4", "A5" are strings and also the content of the lookupRange. Your question is not too clear, from this point of view...