Lookup multiple values in one cell [UDF]
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.
Hi Oscar
This is a very interesting function and helped me a lot so far. My file though is a bit more complicated.
I have multiple info in one cell separated with ";" (example AD1; AD2; AD3) let's say that these are servers (File name SERVERS) and in each server, I have multiple applications.
I have now another file that has all the applications per server per line in excel (each line has one server one application. Filename: APPS).
I want to start from the file SERVERS to look up the servers that are in one cell find them in the second file APPS and bring all the applications also in one cell in the file SERVERS.
Any ideas here?
Thanks in advance
C
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 download the example file.
- Copy VBA code above.
- Open VB Editor (Alt+F11) and select your workbook in the Project Explorer.
- Click Insert on the menu.
- Click Module to create a module.
- Paste code to module1
- Exit VB Editor
How to count word frequency in a cell range [UDF]
This user defined function creates a unique distinct list of words and how many times they occur in the selected […]
Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
List files in a folder and subfolders [UDF]
This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]
Search for a file in folder and subfolders [UDF]
The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]
Split values equally into groups
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
Split words in a cell range into a cell each [UDF]
This post describes how to split words in a cell range into a cell each using a custom function. I […]
Filter unique distinct words from a cell range [UDF]
This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]
Count unique distinct values by cell color
This article demonstrates a User Defined Function (UDF) that counts unique distinct cell values based on a given cell color. […]
Substitute multiple text strings [UDF]
The SUBSTITUTE and REPLACE functions can only handle one string, the following User-Defined Function (UDF) allows you to substitute multiple […]
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
6 Responses to “Lookup multiple values in one cell [UDF]”
Leave a Reply to Oscar
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
When I tried this as the formula is repeats the answer over and over:
=IF(Sheet3!A2=Sheet4!A2,Lookup_concat(D2,Sheet4!$C:$C,Sheet4!$D:$D),Sheet3!G2)
How do i get it to display only once?
Kory
Also when I just do the regular formula it still repeats the results over and over again.
I am trying to use this formula and I get a #name error. I am inserting the code in the general module of the file but it gives me an error an error. I initially tried it, it worked, excel crashed and when I attempt again it does not work anymore.
Yolanda,
I am trying to use this formula and I get a #name error.
You have misspelled the function name or put the code in the wrong module.
I am inserting the code in the general module of the file but it gives me an error an error.
I don't know what "general module" is but you should put your code in the code module. There are instructions in this post, see above.
I initially tried it, it worked, excel crashed and when I attempt again it does not work anymore.
Perhaps Excel was unsuccessful recovering all your data?
Hello, how can we get the same result but without concatenating the lookup.
Look at 5 cells in a row and then return the result, without duplicates in the result ?
Thanks
Roland
Is this what you are looking for?
Array formula in cell E2:
=INDEX($B$1:$B$9, SMALL(IF(COUNTIF($D$2:$D$5, $A$1:$A$9)>0, MATCH(ROW($B$1:$B$9), ROW($B$1:$B$9)), ""), ROWS($A$1:A1)))