This article explains how to perform multiple lookups based on values in one cell with a delimiting character using a User defined Function.

A UDF (User defined Function) is an Excel function that you can build yourself which is great if you can't find a prebuilt one that suits your needs.

You create a UDF the same way you create a macro using VBA code in the VB Editor (Visual Basic Editor), VBA stands for Visual Basic for Applications.

I will explain in greater detail the code I used, where to put it and how to save your workbook,  later in this article.

Worksheet Sheet2 contains the items and the corresponding applications.

Worksheet Sheet1 contains the concatenated items in column A and a formula in column B. The first argument in the UDF is the cell that contains the concatenated values, this cell reference is relative meaning it changes when you copy cell B2 and paste it to cells below.

The second argument is the lookup range and the third argument is the return range, both these arguments have absolute cell references.

Lookup_concat(Search_string,  Search_in_col , Return_val_col )

Formula in cell B2:

VBA code

'Name the UDF and declare arguments and data types
Function Lookup_concat(Search_string As String, _
  Search_in_col As Range, Return_val_col As Range)

'Dimension variables and declare data types
Dim i As Long, result As String
Dim Search_strings, Value As Variant

'Split string using a delimiting character and return an array of values
Search_strings = Split(Search_string, ";")

'Iterate through values in array
For Each Value In Search_strings

    'Iterate through from 1 to the number of cells in Search_in_col
    For i = 1 To Search_in_col.Count

      'Check if cell value is equal to value in variable Value
      If Search_in_col.Cells(i, 1) = Value Then

        'Save the corresponding return value to variable result
        result = result & " " & Return_val_col.Cells(i, 1).Value
      End If

    'Continue with next number
    Next i

'Continue with next value
Next Value

'Return values saved to result to worksheet
Lookup_concat = Trim(result)
End Function

Where to put the code?

You can use the code (see instructions below) in your workbook or get the example file.

1. Copy VBA code above.
2. Open VB Editor (Alt+F11)  and select your workbook in the Project Explorer.
3. Press with left mouse button on Insert on the menu.
4. Press with left mouse button on Module to create a module.
5. Paste code to module1
6. Exit VB Editor

Get the Excel file

Lookup-multiple-values-in-one-cell.xlsm

---

Weekly Blog EMAIL

[newsletter_signup_form id=1]

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

---