## Excel udf: Lookup and return multiple values concatenated into one cell

**Table of Contents**

- Lookup and return multiple values concatenated into one cell
- Lookup and return multiple values concatenated into one cell - ignore duplicates
- Lookup and return multiple values concatenated into one cell - add a comma between each value
- Lookup and return multiple values concatenated into one cell - Wildcard search and ignore duplicates
- Lookup and return multiple values concatenated into one cell - Searching for the first character in a text string

**Richard asks:**

looking for a formula that will take a part number from one column and go and look for all related vehicle applications per that part number and return the vehicle applications to a single cell related back to the part number

**Answer:**

I can´t do that with formula but I can create a user defined function.

### User defined function in cell C2:

+ ENTER

Picture of sheet "Vehicle applications"

### Explaining user defined function

**Lookup_concat(**Look_up_value, Search_in_column, Concatenate_values_in_column**)**

Looks for a value in a column and then returns values in the same rows from a column you specify, concatenated into a single cell.

See picture below.

### VBA code

Function Lookup_concat(Search_string As String, _ Search_in_col As Range, Return_val_col As Range) Dim i As Long Dim result As String For i = 1 To Search_in_col.Count If Search_in_col.Cells(i, 1) = Search_string Then result = result & " " & Return_val_col.Cells(i, 1).Value End If Next Lookup_concat = Trim(result) End Function

**How to implement user defined function in excel**

- Press Alt-F11 to open visual basic editor
- Click Module on the Insert menu
- Copy and paste the above user defined function
- Exit visual basic editor

**Download excel example file**

excel vba - return multiple answers into one cell.xls

(Excel 97-2003 Workbook *.xls)

Is there a way to list speedometer tachometer, etc... on separate lines in the same cell instead of separating them with a space? Similar to hitting the ALT+ENTER to create two lines of info in one cell like this:

cell C2

speedometer

tachometer

Tom,

Sure!

VBA code:

Function Lookup_concat(Search_string As String, _

Search_in_col As Range, Return_val_col As Range)

Dim i As Long

Dim result As String

For i = 1 To Search_in_col.Count

If Search_in_col.Cells(i, 1) = Search_string Then

result = result & " " & Return_val_col.Cells(i, 1).Value & Chr(10)

End If

Next

Lookup_concat = Trim(result)

End Function

Instructions:Select cells.

Press CTRL + 1.

Click tab "Alignment"

Enable "Wrap text"

Click ok!

Oscar,

I just left you a similar comment on another page. Is there a way to do this and put the results into new rows instead of the same cell?

1 Capacitor 1

2 Capacitor 2

Would become:

1 Capacitor 1 Speedometer

1 Capacitor 1 tachometer

1 Capacitor 1 odometer

1 Capacitor 1 fuel gauge

2 Capacitor 2 Speedometer

2 Capacitor 2 tachometer

... and so on

Thanks!

Peter

Oscar thank for the quick response. Is there a way to do this without UDF?

What I am trying to do is make an output matrix which has various wire types listed in column B4 thru B23 and terminating connector types listed across row C4 to AA4. I am trying to populate basically all of my open cells between C4 and AA23 with actual six digit part numbers. In some cases there are two six digit part numbers that must show up in the same cell. The data is being looked up in another tab in the spreadsheet with wire type in column C, part numbers in column B and connector type in column E. I also have a column called Standard flagged with a 1, 0 or black. What I am currently doing is using a combination of Index and Match in my output matrix that looks up a part number based on three criteria, wire type, connector type and a standard part (flagged with 1). If a row meets those three criteria the part number is grabbed and filled into the output matrix. I have that part working, but don't know how to list two part number in the same cell if that condition exists.

Is there a better way of doing this task?

Tom,

You could try to concatenate two formulas in a cell. See this page: http://excel.tips.net/Pages/T002788_Simulating_AltEnter_in_a_Formula.html

Thanks Oscar. Does the UDF stay with the file (embedded) if it is emailed and shared around the office or do you have to setup the VBA code on each individual's machine?

Tom,

The udf stays with the *.xls file.

Oscar thanks for this one. Saved me lot of time

Gonzo,

you are very welcome!

Oscar, I love this and am planning to use the code for a stock checking application. My Question is can it be modified to put the results on separate colums [in line with each search row]

Search Item Item1 Item2 Items 3

Ray, yes it can. But it would be easier to use this formula: How to return multiple values using vlookup in excel with a minor change.

How to create an array formulaCopy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.

Press and hold Ctrl + Shift.

Press Enter once.

Release all keys.

How to copy array formulaSelect cell

Copy (Ctrl + c)

Select cell or cells to the right

Paste (Ctrl + v)

Hi Oscar

I want to use your above formula with a calendar in excel but I keep getting a #VALUE! error.

I have a data sheet that has data simular to this

1/2/2011 Red

1/3/2011 Blue

1/3/2011 Green

1/5/2011 Purple

I copied the lookup_concat udf and my function in column F5 of my calendar is...

=Lookup_concat(F4, Data!A4:A147, Data!B4:B147)

F4 is the field that has my date I was to search for.

Julie,

I am guessing the dates causes problems.

1. Select F4

2. Press Ctrl + 1

3. Click Category: General

4. Remember the value

5. Click Cancel

Select the same date in range Data!A4:A147.

Repeat step 2 to 5.

Are the values the same?

How Excel Stores Dates And Times

Yes they are the same. I does work with vlookup but I want to be able to return multiple values.

This is my vlookup function in another cell and it does work. =VLOOKUP(D4,Data!A4:B378,2,FALSE)

Julie,

I have no idea!

Send me a workbook without sensitive data and I´ll see what I can do.

Wow Oscar, you rule! Searched the internet and beyond looking for this tiny fuction, thank you very much!

Bernard,

Thanks!

Hi Oscar - nice code you've got up here, and great explanation. This is close to what I need, but I'm trying to take multiple input values and output to one cell. I'm doing a skills inventory, where someone could have multiple skills like say Excel and Powerpoint. I'd like to be able to enter multiple numbers and return multiple values - got any tricks for that? Thanks!

Michael,

Yes, open attached file: excel-vba-search-for-multiple-values-and-return-multiple-values-into-one-cell.xls

Oscar - you make this look entirely too easy. I also came up with a way (with some help from a friend) where we changed the lookup key to text and used some string functions to get the desired result w/out VBA. This is more elegant, however. If you like I can post/send the file, I'm just not sure how to do that from this page. Thanks again! - Mike

Downloaded example, but showed #NAME? error in column C under Related vehicle applications.

Looks like an error in the code?

a,

I downloaded the file and it works here.

Is there anyway to get it to ignore duplicates entries?

so that each 'vehicle application' is listed once in the 'related vehicle applications' no matter how many times it appears in the 'vehicle application' list?

Matt,

Yes, download example file:

excel-vba-return-multiple-unique-values-into-one-cell.xls

How would you integrate the multiple criteria lookup_concat function with the above mentioned functionality to remove duplicates?

Thank you Oscar

that has proved to be very useful

Hi Oscar thank you for your code it works well for me with a small exception. I have used your code shown to Tom (26th Jan 2011). When the code concatenates two or more text strings the text is placed on successive lines but there is always a ALT + ENTER character at the end of the text that adds an extra line below the last text string.

Is there any way to prevent the this last ALT + ENTER from being added?

Thank you for your help

Chris,

Search_in_col As Range, Return_val_col As Range)

Dim i As Long

Dim result As String

For i = 1 To Search_in_col.Count

If Search_in_col.Cells(i, 1) = Search_string Then

result = result & " " & Return_val_col.Cells(i, 1).Value & Chr(10)

End If

Next

result = Left(result, Len(result)-1)

Lookup_concat = Trim(result)

End Function

Oscar, Very Nifty, Thanks

Modified to insert commas & ampersand:

Function Lookup_concat(Search_string As String, _

Search_in_col As Range, Return_val_col As Range)

Dim i As Long

Dim n As Integer

Dim c As Integer

Dim result As String

For i = 1 To Search_in_col.Count

If Search_in_col.Cells(i, 1) = Search_string Then

n = n + 1

End If

Next

For i = 1 To Search_in_col.Count

If Search_in_col.Cells(i, 1) = Search_string Then

c = c + 1

Select Case c

Case 1

result = Return_val_col.Cells(i, 1).Value

Case n

result = result & " & " & _Return_val_col.Cells(i, 1).Value

Case Else

result = result & ", " & Return_val_col.Cells(i, 1).Value

End Select

End If

Next

Lookup_concat = Trim(result)

End Function

This is great. How do I make this UDF available for all my spreadsheets without having to inserting it into each spreadsheet?

This is so wonderful. Is there a way to add commas between each entry?

Vicki,

Hi, this works great with the comma though is it possible to not start the result with a comma as it appears to do but end with a comma in the result?

Rich,

Excel toolbox: Save your custom functions and macros in an Add-In

Thanks Oscar, I keep getting #VALUE! I believe this is happening because some of the search fields have #NAME errors. Any way to modify the script to ignore errors and continue looking?

Hi Oscar,

You are unbelievable with this! I have a question....

What if the return values from the function (Return_val_col) were integers and instead of listing all of the separate values in one cell, the function returned the sum of the values...

do you know how I could do that using the function that you created?

Naajia,

I attached both a vba and a formula solution. I do recommend using the formula.

http://www.get-digital-help.com/wp-content/uploads/2010/12/Naajia.xls

Oscar -- found this with a Google search, and it is EXACTLY what I needed. THANK YOU for your contributions here!!! Saved me a TON of time on a vital project.

Thank you Oscar.

Found this the same as Brad with Google search.

This very nice and clearly illustrated example helped me quite a bit.

Hello,

I was wondering if you could help me change the column look-up to a row based look-up. Instead of searching in one column I would like to search in 1 row for a certain number. Then display the results just as you have done.

Thank you.

Katherine

Oscar,

Like the rest, I'm thrilled to find your formula. Could it be modified to have a second (and maybe third) search column, kind of like a COUNTIFS() function would do?

Thanks,

Peter

Peter,

Did you figure out a way to set this up with multiple criteria?

Where I've put 17, just include the number of columns further away.

Option Explicit

Function Lookup_concat(Search_string As String, _

Search_in_col As Range, Return_val_col As Range)

Dim i As Long

Dim result As String

For i = 1 To Search_in_col.Count

If Search_in_col.Cells(i, 1) = Search_string Then

result = result & ", " & Return_val_col.Cells(i, 1).Value & " " & Return_val_col.Cells(i, 17).Value

End If

Next

Lookup_concat = Mid(result, 3)

End Function

Here is a link to an article I wrote in a "mini" blog I host which contains a UDF that I derived from this blog article's code but to which I added several additional optional arguments that provide some useful (I hope) flexibility when performing your lookup...

http://www.excelfox.com/forum/f22/lookup-value-concatenate-all-found-results-345/

Katherine,

Yes, see attached example file!

excel-vba-return-multiple-answers-into-one-cell-horizontal-lookup.xls

Rick Rothstein (MVP - Excel),

I tried your udf and all the optional arguments. It works great, I am sure it will be useful!

Hi Oscar,

Thanks very much for this UDF. Very useful. Is there a way that you know it can be used in a data validation list? I get an error if I use it and I read somewhere else that it's not possible to use UDF's. Basically I have a large table with fields 'country', 'operator', 'plan'. In another table I want to select a country, then in a second column get a list of (unique) operators available in that country and select one, and in a third column then select a plan based on the country operator choice in the other columns. Your UDF (with the appropriate separator and a little tweaking perhaps) would be ideal for that, but I need to find a way to use it in selection lists.

Thanks,

Mario.

Mario Hoek,

I think you will find these posts interesting:

http://www.get-digital-help.com/2010/07/17/create-dependent-drop-down-lists-containing-unique-distinct-values-in-excel/

http://www.get-digital-help.com/2012/02/22/dependent-data-validation-lists-in-multiple-rows/

Oscar,

First off, thanks so much for all your help. Your site has helped me many times.

My question stems off of Tom's question, and I've basically used the same code you've provided to Tom. My problem is, I have a range of a week (e.g. 5/7/12 - 5/13/12, 5/14/12 - 5/20/12, etc.) and from a list of individual dates, I have to determine if a date falls into that range, then it needs to return the corresponding text for each of those dates within the same cell (concatenated).

So if I have a range of 5/7/12 - 5/13/12, I need the macro to look at a list of dates, determine which of the dates fall between that range, and return the text in the adjacent column to that individual date.

Thanks again!

Jonah,

VBA Macro

Download excel *.xslm file

Concatenate-values-within-matching-date-ranges1.xlsm

Oscar,

Thanks so much for replying. My output would actually need to look more like a concatenated form of column F.

Column B would actually be a non-concatenated search input (with one date for each text).

For example, given the date range from D2 to E2, the macro should look up which date in A2:A5 corresponds to that range, and return the concatenated form of each of the text.

See the excel file: https://docs.google.com/open?id=0B0B7Aw7pD4WCanJlMnlJbHpQRDQ

Thanks again!

Oscar,

Kindly disregard the last post. I've figured it out using your explanation on this page and other pages on your site.

I greatly appreciate the help you've so graciously given.

Hi Oscar

I am using your function Lookup_concat to fetch data from some other excel file. But I am facing a problem. If I use Vlookup (built-in excel function), then I get the result even if source file is closed. But Lookup_concat function only gives result if source file is opened, otherwise it gives #VALUE!

Pls help me here.

Thanks

Amit Gandhi

Amit Gandhi,

VBA does not support accessing information from closed workbooks.

Links:

Accessing ranges in closed workbooks in custom functions

INDIRECT and closed workbooks

Excel Automation: How to use an external link as an argument in a user-defined function?

Hi Oscar

I read your links provided, but i am unable to get the desired result, as I am not very much expert in VBA.

One link is suggesting to use ADO, other is suggesting to use HYPERLINK (When an Excel workbook is closed, it cannot be referenced by the INDIRECT function, however as Greg states this can be achieved via an acrimonious HYPERLINK function without having to resort to VBA/coding of any kind.)

Can you please help me how to modify LOOKUP_CONCAT function to get result from closed workbooks as well.

Amit Gandhi,

I would happily help you out but I have no clue.

Thanks Oscar for your valuable time.

Hi oscar

Thanks for your helpful website.I need to lookup in one column and return the results of two other columns.also I need to lookup in one column and return the results of two other column if the date in datecolumn in that row is equal to date in cell B2.I modified your vba code but i don't know it is correct or not beacuse when i put it in my spreadsheet it take a lot of time to calculate.and sometimes didn't work and return error value.

I need your comments.

here is my sample data:https://docs.google.com/open?id=0B6n9ww2vwHPMSFZwelNDRGt0Nmc

Hi Oscar,

Thanks for informative post. I need to lookup values from Column C (ticket #) based on Column A (Date) and Column B (Person). The look-up could return multiple values from column C (multiple tickets for a date). I need to concatenate multiple values in one cell of date –person matrix. UDF discussed here works but only problem is that it is not doing lookup on multiple columns. Can you please help?

Thanks in advance

Thank you for this - it's a brilliant, simple solution that works a treat.

Hi Oscar -

TO echo the question Peter posted in March - is there anyway to modify this formula and UDF so that it searches multiple criteria in 2 different columns?

This UDF is FANTASTIC!

Jen

I am on it.

Thanks!

Hi Oscar! Are you having any luck with this? I've looked everywhere for help with this and nothing...I'm counting on you!

Thanks!

Just discovered that this is case sensitive - I was confused that it wasn't finding things that other Excel functions (such as VLOOKUP) can find. How can I make it ignore the case of a letter? To force the source and the user input to be the same is not practical, unfortunately.

Ignore previous comment. To resolve the case sensitive issue, pop "Option Compare Text" on a line at the top of the module and searches will not be case sensitive.

Oscar, you are a life saver. I did have to change on line of code to get it to work for my needs.

From:

result = result & "," & Return_val_col.Cells(i, 1).Value

To:

result = Return_val_col.Cells(i, 1).Value & "," & result

Works fine now, but I one slight change would make it perfect:

Is there a way to make it only return UNIQUE values? Instead of:

207,207,205,206

It would say

207,205,206

I posted a link to a function I developed earlier in this thread which will allow you to do what you have asked for. Here is that link again...

http://www.excelfox.com/forum/f22/lookup-value-concatenate-all-found-results-345/

Rick,

Is your function able to search based on multiple criteria and return multiple values concatenated into one cell?

This is what I'm desperate to find an answer for!

Thanks,

It is a function, so you can call it for each of your search words and concatenate the results together (if you have a lot of search words, you may need to construct a loop to process them efficiently). If you want me to add additional functionality to my code, post the request against my mini-blog article over in my blog-site's forum location and I will attempt to comply there.

http://www.excelfox.com/forum/f22/lookup-value-concatenate-all-found-results-345/

Hi Thanks for this,

How would change search_string to lookup any value greater than 0?

Hi Oscar,

The code works great, thank you! I keep getting #VALUE for items that do not have a match.

I have a calendar and some things are in progress, planned, etc. If for a day, there are no planned items, I want it to be blank in the planned column...can't seem to figure it out. Can solve it with IF(ISERROR), but would like to incorporate into UDF, and can't seem to figure it out.

Many thanks!

Back awhile ago in the comment section for this blog article, I posted a link to an alternative UDF to the one Oscar posted which provided some extra functionality. One of the things my UDF does is return the empty string ("") when the text being searched for cannot be found. Here is a link to my mini-blog article where I posted that alternative UDF code for your consideration...

http://www.excelfox.com/forum/f22/lookup-value-concatenate-all-found-results-345/

thanks!

Christy,

I tried the udf for items that don´t have a match and I get a blank cell in those cases. I am using excel 2010.

I am using Excel 2007. The code Rick referred to worked, but it makes Excel hang way too much.

Yes, I have found the same issue.

I am having the problem that this code only looks in formulas not values, whereas Rick's does look in the values and finds what is needed... however, I can't use his code as it just crashed the computer because of the large amounts of data I am dealing with! :(

Is there any way to adapt this module to look in values?

Hi I was wondering if instead of showing the results separated by a space in on cell, I could make the results added to eachother in one cell.

I'm trying to lookup more than one value (number,e.g.prices)but show it added to all the other results.

thanks in advance

@Ralfy - I am having trouble visualizing what you are asking for... can you post a small sample of data and show us what you want that data to look like after it has been processed?

I have a small table where the data get imputed it has a row for the name of each person in charge of getting sales and credits for a 4 hrs period (4 to 5 rows) the columns are: name of person, amount of credits, $ sale up to that time, sale for the person ( has a formula that takes away what anyone before makes to know what this person sale is, credit productivity (sale/credits)

Then there is worksheet for each person where it looks up the sales, credit and productivity for that person for the day.

All of that I already have set up using vlookup. My issue is when the data sheet has more than one entry per day per day. I would like the lookup function to recognize more than one entry and add them up then insert to the persons worksheet.

Hope that helps,

Thank you in advance for your help, I hope to resolve this issue soon.I have a file, Cells A1:A50 have multiple e-mail addresses separated by ";". On Column B, I have a list of 1,000 e-mail addresses, each cell on column B has only one address. What I am trying to get to, is on Column C, to see which e-mails from cell A1 are found in the entire column B. Then which e-mails from cell A2 are found in the entire column B, and so on. If I need to send a spreadsheet please let me know. Thank you for your help.

Give this a try... put the following formula in C1 and copy it down:

=IF(COUNTIF(A:A,"*"&B1&"*"),"X","")

Ha, dummy me, I was thinking it too complicated, with Index and Match formulas. Should have thought the other way around, many thanks for your help.

Alright, here is the next step on this. Now that I can find which individual e-mail address from Column A is listed in the entire column B, I need to do a look-up and give me the corresponding category listed on column C.

Column A Column B Column C

e-mail1 e-mail1;e-mail2;e-mail4 CatA

e-mail2 e-mail3;e-mail6;e-mail7 CatB

How would I go about finding which value from Column A, is listed in Column B and then list it's corresponding value from column C?

Thank you in advance for your help with this.

Samsam,

e-mail1;e-mail2;e-mail4

What is this? Three emails in the first cell in column B or where are they entered?

Correct, Column A cells have individual addresses that are listed somewhere in the multitude of e-mails from Column B, which then have a corresponding category in column C. So while column A lists only 1 e-mail per cell, Column B cells have anywhere from 2 to 10 e-mail addressed in one cell. Then column C shows the category in which those e-mails belong.

SamSam,

I moved cell range B1:C6 to C1:D6. The formula in cell B1: =INDEX($D$1:$D$6,MATCH(A1,$C$1:$C$6,0))

Hi Oscar,

I've been trying to find the solution for my lookup problem for a while now and you seem like the right person to ask... Your lookup code works great (thanks) but I need to do two or three lookups within identified matching records... in other words:

Sheet 1 - 'File data'

1. client name

2. filename

3. file date create

Sheet 2 - 'Client data'

1. client name

2. client ID

3. service start date

4. service end date

I need to map correct client ID based on lookup by client name and then based on finding which service date range does client file created date fit into.

So I need to:

1. First search - Identify Client records with matching name

2. Second search - Within that range, I need to find fitting date range.

Your lookups are great when I search entire sheets but I need to do second seach based on subset of data.

Any help will be much appreciated.

Thanks!

Nena

Nena,

read this:

Search a table and use the returning value to search another table

Thanks Oscar,

regards,

Nena

[...] tableFiled in Dates, Excel, Search/Lookup on Sep.12, 2012. Email This article to a Friend Nena asks:Hi Oscar,I've been trying to find the solution for my lookup problem for a while now and you seem [...]

Thanks for posting very nice and effective UDF.

I had to change one line of code to get it to work for my needs.

From:

result = result & " " & Return_val_col.Cells(i, 1).Value

To:

result = result & "," & Return_val_col.Cells(i, 1).Value

but cannot omit last comma from the returned value. Any help in this respect will be highly appreciated. Thanks in advance.

sorry again after changing one line return value display like :

10, 12, 10,

but I want to omit last comma which will return like :

10, 12, 10

Thanks in advance

You get a **trailing** comma with that code line, not a LEADING comma??? You should double-check that as that code line can only produce a leading comma. And the way to get rid of it is by changing the last line of code from this...

Lookup_concat = Trim(result)

to this...

Lookup_concat = Mid(result, 2)

I know the number of comments for this article are quite long, so you may have missed the link I posted to a function I developed which extends the functionality of Oscar's UDF by adding additional options, so you might want to check it out here...

http://www.excelfox.com/forum/f22/lookup-value-concatenate-all-found-results-345/

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) lets 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. File name: APPS).

I want starting 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

Chrisa,

see this post:

Lookup multiple values in one cell (vba)

Hi Oscar

the module seems to be looking in formulas by default rather then in values, which means it does not find any of the data in my fields (as they are all generated by concatenate formulas!)

You don't happen to have a fix for this by any chance???

Many thanks for your help

F

Hello again Oscar,

I just realised that the UDF does look in values... but it does not work on my sheet that contains xml data... it just returns #value

Hi Oscar,

I used your code for the option explicit fuction lookup_concat as well as the function unique. My problem lies in when i incorporate that into a nested formula:

=IF(D5"PO",Lookup_concat(B5,$B$2:$B$5000,$F$2:$F$5000),F5)

the formula works perfectly in the cells, but once i put that into the vba it gets stuck in an eternal loop and goes from

please help me.

my formula was supposed to read:

Valerie,

I tried a nested formula and it works here (excel 2010).

ok, so I realized that when I was watching my macro run step by step using F8 it appeared to be stuck in that loop once I hit your function. Once I just ran the macro (including your function) it worked perfectly. thank you for checking that. Do you have a place where I could continue to ask you questions with excel unrelated to this function?

Thank you so much again for your help.

valerie,

Do you have a place where I could continue to ask you questions with excel unrelated to this function?

No, most people search my site for answers. If they can´t find what they are looking for, they ask questions in blog posts.

I have a similar problem, but am finding that to search within vs for an exact value is causing the CONCAT formula to not work? Trying to look for value D2 withing column 2 of a Table1, then to return all values in column 3 of the table. Don't care if there are commas separating them, etc. Forumla only seems to work if it looks for an exact match. How do I change the following?

=Lookup_concat(D2&"*",'Table1'!B2:B7,'Table1'!C2:C8)

What am i doing wrong? The formula is returning nothing each time, though not getting any error??

Thanks in advance...

Additional question on this: the formula also does not seem to allow me to use named ranges vs selecting the range each time. Is there a way to update the formula to allow for named ranges?

Thanks...

Hi Oscar,

you are unbelievable! THANK YOU SO MUCH for all the answers!

[...] Chrisa asks: [...]

Hi Oscar.. Is there any way to use VLOOKUP for multiple criteria and Ido not want to use CVS... thanks in advance...

Kamran Mumtaz,

I read your question:

http://www.mrexcel.com/forum/excel-questions/682187-sumifs-unique-multiple-search.html#post3379273

This is the post you are looking for:

http://www.get-digital-help.com/2009/01/14/automatically-filter-unique-row-records-from-multiple-columns/

Is there any way to use VLOOKUP for multiple criteria and I do not want to use CVSI assume you don´t want to use CSE? (Ctrl + Shft + Enter) No, not to my knowledge.

Why did not you reply if you saw the question on Mrexcel board...? Many thanks for your help...

This is the formula given by Aladin Akyurek without (CSE)...

=INDEX(Sheet3!$B$2:$B$65,

MATCH(1,INDEX((Sheet3!$C$2:$C$65=E$1)*

(Sheet3!$A$2:$A$65=$A3),0,1),0))

Kamran Mumtaz,

Why did not you reply if you saw the question on Mrexcel board...?A trackback is created when someone links to my website. That´s how I discovered your thread.

This is the formula given by Aladin Akyurek without (CSE)...That formula is so interesting that I made this post:

No more array formulas?

Hi Oscar I have a list of numbers like

923005054609

913005054609

923005054609

933005054609

923005054609

993005054609

953005054609

923005054609

923005054609

993005054609

923005054609

973005054609

923005054609

923005054609

I do not want those numbers which starts 92... hope I am making sense...

Thanks in advance

Kamran Mumtaz,

Array formula in cell C4:

=INDEX($A$1:$A$14, SMALL(IF(LEFT($A$1:$A$14, 2)*1=$D$1, MATCH(ROW($A$1:$A$14), ROW($A$1:$A$14)), ""),ROW(A1)))

Formula in cell D4:

=INDEX($A$1:$A$14, SMALL(INDEX((LEFT($A$1:$A$14, 2)*1=$D$1)*(MATCH(ROW($A$1:$A$14), ROW($A$1:$A$14)))+((LEFT($A$1:$A$14, 2)*1)<>$D$1)*1048577, 0, 0), ROW(A1)))

Download excel file

Kamran-Mumtaz.xlsx

HI Oscar thanks for the formula but I want the numbers which do not start from 92...

Hey I made a little change in the formula and got the desired result

=IFERROR(INDEX($A$1:$A$14,SMALL(IF(LEFT($A$1:$A$14,2)*1$D$1,MATCH(ROW($A$1:$A$14),ROW($A$1:$A$14)),""),ROW(A1))),"")

Thanks a lot man... :)

Kamran Mumtaz,

I am sorry!

=INDEX($A$1:$A$14, SMALL(IF(LEFT($A$1:$A$14, 2)*1<>$D$1, MATCH(ROW($A$1:$A$14), ROW($A$1:$A$14)), ""),ROW(A1)))

[...] Mumtaz asked: Is there any way to use VLOOKUP for multiple criteria and I do not want to use [...]

I love this function, but know very little about VBA. Can anyone suggest a way to tweak the code a bit so that the return results are delimited with a semicolon and a space, rather than just a space?

Thanks!

Elizabeth,

Hello Oscar,

thanks for your code , i use it for a file for same searching values and it Work fine.

all the searched data are numbers :

56|55|40|63|....

for exameple i only wana one value that is : < or = a value of an other cell ( 57) in this case i only get : 56 .

could you please give me an edit code.

thanks in advance

Hey Oscar,

This function looks like it's going to do exactly what I need it to do, however when I use it I get in my list I get #VALUE. I believe it's because the returned values are multiple email addresses ([email protected]). Is there anyway this would work with email addresses?

Joe,

This function looks like it's going to do exactly what I need it to do, however when I use it I get in my list I get #VALUE.I am not sure whats wrong, maybe you don´t use absolute cell references in the function?

I believe it's because the returned values are multiple email addresses ([email protected]). Is there anyway this would work with email addresses?I am sure it works with email adresses and duplicate email adresses.

Oscar et al., thank you for this on-going forum. It has been incredibly helpful! I have (what I hope to be) a simple question. I modified one of the posted UDFs so that the multiple outputs (in this case, character strings) are displayed in a single cell, with each character string led by a bullet and followed by a hard return (i.e., ALT-Enter). I'm using the following code:

Function LOOKUP_CONCAT(Search_string As String, _

Search_in_col As Range, Return_val_col As Range)

Dim i As Long

Dim result As String

For i = 1 To Search_in_col.Count

If Search_in_col.Cells(i, 1) = Search_string Then

result = result & "• " & Return_val_col.Cells(i, 1).Value & vbLf

End If

Next

result = Left(result, Len(result) - 1)

LOOKUP_CONCAT = Trim(result)

End Function

The problem is that if the originating cell is empty, a bullet still appears. Is there a way I can modify the above code to eliminate the bullets for empty cells?

Thanks in advance for help!

Amanda,

try this:

Oscar,

Thank you for your quick response! The above code is sooo close... Instead of bullets, the blank cells now report "#VALUE!". Preferably, the blank cells would just be empty, but perhaps I can play around with the formula a bit.

All the best,

Amanda

Amanda,

Just in case I can save someone else a bit of time:

I used an IF function in combination with IFERROR to force Excel to report blank cells. For example:

IF((IFERROR((LOOKUP_CONCAT(A30,Database!A29:A617,Database!O29:O617)),"None"))="None","",(LOOKUP_CONCAT(A30,Database!A29:A617,Database!O29:O617)))

Hope that helps!

Thanks again!

Amanda

Hi Oscar. Thank you so much for sharing your extensive knowledge with us. I have a question. I am using that function that you gave for adding the values of the cells that I lookup .... =SUMPRODUCT((I5=C4:C32)*D4:D32)(Combined with the vba).... But I also have a need to find the average of those cells. Do you have a formula for that?

Thanks, Carla

Carla,

I don´t understand, can you explain in greater detail?

Hi Oscar,

This formula seems to work down a column, but I can't get it to work across rows. How would you amend the basic formula at the top so that it worked to compare 2 rows?

Thank you for sharing your knowledge. This is extremely helpful.

Eric,

I am not sure I understand.

Is this what you are looking for?

Hi Oscar,

I have a big excel file (around 22000 rows and 20 columns). Below I have tried to represent it in simplified way. Left hand side is my raw data and Right hand side is my desired output. If you can help me do this using functions (no vba code) that would be great. Please note all data is text.

I tried to upload the image but, does not look like it worked. Let me know how this can be resolved.

Nilesh,

use this contact form:

http://www.get-digital-help.com/excel-consulting/

Thanks Oscar.. I made a similar code to make it work like vlookup

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) & ","

End If

Next i

SingleCellExtract = Left(Result, Len(Result) - 1)

End Function

Thanks for the cues I got from here

Sumit Bansal,

thanks for sharing!

Hi Oscar,

This has been a great help to me. I'm trying to get it to do one thing that I can't figure out though. For my application, almost all the lookup values are "1". I do however have a few instances where the lookup value could be 2 or 3. Is it possible to to use a range, e.g. 1-5 for my lookup value? Thank you.

Will,

Download *.xls file

http://www.get-digital-help.com/wp-content/uploads/2010/12/excel-vba-return-multiple-answers-into-one-cell-using-a-search-range1.xls

Oscar,

its really great and helpful function will save lots of my time... Thanks

Had an query, i want to use this function within my macro by calling it, How can i do define 'Search_string', 'Search_in_col', 'Return_val_col' as an input and run this function cell by cell?

Thanks again...

Got it no need to reply... thank-you... thank-you very much.. god Bless u.

Oscar,

I love the code but I actually need to return multiple values meeting multiple criteria. (I think this is what Jen was looking for)

E.g.

If columnA=X AND columnB=Y then return columnC values concatenated into single cell.

Thanks

Still unclaer as to how I would achieve this.... if anyone could enlighten me I would be extremely grateful!

This is what I have at the moment

But I need an additional logical test in there...

E.g. If columnA=X AND columnB=Y then return columnC values concatenated into single cell.

Thanks

Adam,

try this:

Download excel *.xlsm file

excel-vba-return-multiple-answers-into-one-cell-v3.xlsm

Amazing! Thank you!

[…] are some near solutions that I found but they consolidate the column data and not the row data. http://www.get-digital-help.com/2010/12/20/excel-udf-lookup-and-return-multiple-values-concatenated-… http://www.ozgrid.com/forum/showthread.php?t=94895&p=445111#post445111 […]

Works great !!!

Just what i was looking for. Clean and simple.

Mike,

Thanks!

I cannot get the UDF to work properly within my file for the life of me. I need the values to populate with the project name on a project timeline chart based off the project schedule. Similar to the post on May 25, 2012 but it does not work in my sheet. It is not recognizing the date range and multiple values.

Here is what I am trying to do:

start date col B; project name col C;

timeline chart

beginning date col G; end date col H

I need the project names which will populate col I based off the start date of the project. Multiple dates should be concatenated to the proper month range in col I

Basically, if i have three projects starting on any date in January the project names should populate for that month in col I.

Here is the formula I used: =Lookup_concat(B3,$G$3:$G$28,$H$3:$H$28,$C$3:$C$28)

Here is the UDF code:

HELP!!!!! Thanks in advance....

My Column E contains comments on each row (E2 has comments/text, E3, has comments, text). I also have a list of available keywords (O2:O2330). In H2, I want the formula to look at E2 and return each keyword (O2:O2330) contained in the text field (e.g., E2), ideally with comma separation.

Currently, I am using the following formula, but it only returns one of the keywords and not all the keywords

=LOOKUP(1E+100,SEARCH(O2:O2330,$E2),(O2:O2330))

What formula do I need to check all the cells in Column E and bring back all the keywords from each cell?

I found this via google, and tried copying the vba code into my excel 2010 spreadsheet - BUT unfortunately, getting the "#NUM!" error. I modified and changed "," with";" without success. Is there anything wrong I could be doing?

chander,

Did you paste the code into a module?

1. Press Alt + F11

2. Click "Insert" on the menu

3. Click "Module"

4. Paste the code into the module

Firstly - thank yous so much for this UDF - it is almost exactly what I needed.

I'm new to using VB and have tried adding both your duplicate and comma samples above but this seems to be confusing the calculation.

What do I need to do to be able to both ignore duplicates and add a comma between each value?

Kind regards

Amber Dixon

VAChampion

Amber,

What do I need to do to be able to both ignore duplicates and add a comma between each value?

See Matt's comment:

http://www.get-digital-help.com/2010/12/20/excel-udf-lookup-and-return-multiple-values-concatenated-into-one-cell/#comment-40011

and Vicki's comment:

http://www.get-digital-help.com/2010/12/20/excel-udf-lookup-and-return-multiple-values-concatenated-into-one-cell/#comment-42471

Oh also.

The original lookup value I have sometimes appears in a cell with other numbers and it doesn't seem to be picking up these particular options when concatenating together. Is there any way for these others to be picked up as well?

In order to do this in one of the other cells I had to add asterix's etc ("*"&B96&"*") to make it work - assuming this works the same how would I add this to VB?

Kind regards

Amber Dixon

VAChampion

Amber,

The original lookup value I have sometimes appears in a cell with other numbers and it doesn't seem to be picking up these particular options when concatenating together. Is there any way for these others to be picked up as well?In order to do this in one of the other cells I had to add asterix's etc ("*"&B96&"*") to make it work - assuming this works the same how would I add this to VB?

Try the following udf:

Download excel *.xlsm file

excel-vba-return-multiple-unique-values-into-one-cell-wildcard-search.xlsm

@Amber,

You may have missed my earlier message as it is buried in the slew of responses this thread has gotten, but I posted a link to a mini-blog article I wrote that was derived from this thread which will allow you to control all the things you mentions. Here is the link to my article...

http://www.excelfox.com/forum/f22/lookup-value-and-concatenate-all-found-results-345/

Read the accompanying text as it will tell you everything you need to know, but just to emphasize... make sure you set the Delimiter argument to a comma, the MatchWhole argument to False and the UniqueOnly argument to True along with any other arguments.

Hi Oscar, great UDF. Is there a similar code to search in rows and return the values to one cell?

Hi Oscar, great UDF. Is there a similar code to search in rows and return the values to one cell

Hi Oscar,

great UDF. Is there a similar code to searches rows?

Function LOOKUP_CONCAT(Search_string As String, _

Search_in_col As Range, Return_val_col As Range)

Dim i As Long

Dim result As String

For i = 1 To Search_in_col.Count

If Search_in_col.Cells(i, 1) = Search_string And Return_val_col.Cells(i, 1).Value "" Then

result = result & "• " & Return_val_col.Cells(i, 1).Value & vbLf

End If

Next

result = Left(result, Len(result) - 1)

LOOKUP_CONCAT = Trim(result)

End Function

Hi thanks for this above code. But I need to return concatenate using multiple criteria rather than single criteria.

Oscar - This was extremely helpful - many thanks

Danny,

thank you for commenting.

Can't say thank you enough.

Tom,

I am happy you find it useful.

Hi Oscar,

Thank you so much for this code, it's amazing! I have a fairly large table that I want to use this on, but keep getting a #VALUE error. I've played around with the inputs and noticed that I start getting the error at certain range sizes. For example, if the range is only 100 rows (say E3:E103) it would work just fine, but the moment it hits a random number (and this number seems to vary by the spreadsheet I use it in), usually anything over 500 rows, then I get a #VALUE error. Any thoughts? The values it's returning are quite wordy since they are security roles to a system. I'm trying to think if there is a character limit I'm hitting somewhere? Or maybe there is a row limit? I've tried this with your original code, and with my modified code (below). In your original spreadsheet I expanded your sample data to be more than 2000 rows and it worked, but with larger Excel files and larger tables it seems to start getting wonky.

Alexmp,

I've played around with the inputs and noticed that I start getting the error at certain range sizes.For example, if the range is only 100 rows (say E3:E103) it would work just fine, but the moment it hits a random number (and this number seems to vary by the spreadsheet I use it in), usually anything over 500 rows, then I get a #VALUE error. Any thoughts?

The values it's returning are quite wordy since they are security roles to a system. I'm trying to think if there is a character limit I'm hitting somewhere? Or maybe there is a row limit?How wordy?

Excel 2007, 2010 and 2013 have a total number of characters that a cell can contain. 32,767 characters.

32,767 characters

They are wordy, but not that wordy. I can't seem to figure out what the problem might be. It really shouldn't matter because the string it returns won't be that long. Even if it might be for 1 or two of the cells that it would return values for, it wouldn't do it for all of them, so theoretically the ones that have smaller values should still show up. However, when I increase the range size it gives me the #VALUE error for all of the cells I use the function in, even if the value it's supposed to return is blank.

Any thoughts on what to look for?

HI Oscar,

ALT + ENTER Code resulting un-necessary space, pls can you tell me how I can remove that space.

Now

speedometer

tachometer

odometer

odometer

fuel gauge

& Require

speedometer

tachometer

odometer

odometer

fuel gauge

Hi Oscar,

Glad to find your code of "Lookup and return multiple values concatenated into one cell - ignore duplicates".

It is very close to what I need for my workbook. I have to lookup the values from several worksheets and remove any duplicates from the results.

Is there a way to do this?

Thanks in advance!

Hi Oscar,

Very beautiful UDF. It helped me a lot.

Is it possible to add sumifs into this UDF.

Example:

Column A is having Names, Column B is having Location and Column C is having Total Amount.

Result by using the UDF am getting desired result.

For each Name, number of locations is dynamic, for some criteria there could be 1 location for some it could be 3 and so on.

In column D, I need to get sum of Amount based on each resultant value from the LookUpContact result.

Please help me

Thanks,

Kumuda

Oscar,

Thank you so much for sharing this! It was exactly what I needed up until I needed to retrieve multiple values based on criteria. I just don't know how to accomplish this.

Here are my data columns:

Supplier Buyer Buyer Need

I have a long list of Suppliers who are meeting with Buyers on specific Buyer Needs. Some Suppliers are meeting on 1-5 different Buyer Needs. I need Column C (Buyer Need) to return a concatenated list with new lines for each need. However, I only want the needs of Buyer 1 related to Supplier 1.

I have this:

Supplier 1 Buyer 1 Need 1

Supplier 1 Buyer 1 Need 2

Supplier 1 Buyer 1 Need 3

Supplier 1 Buyer 2 Need 2

Supplier 1 Buyer 2 Need 4

Supplier 2 Buyer 1 Need 4

Supplier 2 Buyer 1 Need 5

Supplier 3 Buyer 2 Need 6

Supplier 3 Buyer 2 Need 7

This is what I'm hoping to get:

Supplier 1 Buyer 1 Need 1

Need 2

Need 3

------------------------

Supplier 1 Buyer 2 Need 2

Need 4

------------------------

Supplier 2 Buyer 1 Need 4

Need 5

------------------------

Supplier 3 Buyer 2 Need 6

Need 7

-----------------------

Thank you!

Sorry, the spacing is off... the Need 1, Need 2, Need 3, etc. are in the same column in a single cell.

Just like to thank you for this solution which i was badly looking for and actually using it today

Oscar, this formula is the BEST THING EVER! One question though. If I only need to return values that start with a certain letter, how can I integrate that into the function?

Kaley,

VBA Code

Download excel *.xlsm file

excel-vba-return-multiple-answers-into-one-cell-starting-with-a-specific-character.xlsm

Hi Oscar,

Thank you very much for sharing the formula, I am so lucky to bump to this website as this is the formula that I exactly need.

However, I have problem on the excel report processing upon enabling the macro. is there any limitation in number of rows in database?

Hi Oscar, I love your website!

Is it possible to vlookup a single cell from another single cell containing multiple items separating by a comma? For instance, I'd like to know what color the numbers 11,2,33 are...

Number Color

1,11 green

2,22 orange

3,33 blue

Hi Oscar,

I don't have any experience in UDF/macro/vba. I copied vba code (lookup and return multiple values in one cell) and it works well and solve my problem.

However, I face another problem; the excel file take very long processing time to change values related to the UDF code as I saved the file in Macro enable worksheet (Microsoft Excel 2013 - file format .xlsm). if I saved in xlsx format, the processing time is fine (the file size is only 800kb), but the function (UDF) is not working at all.

Could you please advice me on how to resolve this problem? I did follow your steps (copy and paste the vba code)...

Thank you very much for your kind help...