Excel udf: Lookup and return multiple values concatenated into one cell
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)
Related posts:
Lookup multiple values in one cell (vba)
Lookup values in a range using two or more criteria and return multiple matches in excel
Lookup multiple values in different columns and return a single value
Lookup and return multiple values from a range excluding blanks
Lookup a value in a list and return multiple matches in excel
Recommended categories
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 formula
Copy (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 formula
Select 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
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?
Rich,
Excel toolbox: Save your custom functions and macros in an Add-In
Vicki,
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 result = Left(result, Len(result)-1) Lookup_concat = Trim(result) End FunctionThanks 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?
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
Function Lookup_concat(SearchDate As String, _ StartDate As Range, EndDate As Range, Return_val_col As Range) Dim i As Long Dim result As String For i = 1 To StartDate.Count If StartDate(i, 1) <= SearchDate Then If EndDate(i, 1) >= SearchDate Then result = result & " " & Return_val_col.Cells(i, 1).Value & Chr(10) End If End If Next i result = Left(result, Len(result) - 1) Lookup_concat = Trim(result) End FunctionDownload 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
For i = 1 To Search_in_col.Count If Search_in_col.Cells(i, 1) = Search_string Then temp(UBound(temp)) = Return_val_col.Cells(i, 1).Value ReDim Preserve temp(UBound(temp) + 1) End If Nextplease 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.
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 CVS
I 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,
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 FunctionHello 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 (name@domain.com). 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 (name@domain.com). 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:
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 FunctionOscar,
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,
Function LOOKUP_CONCAT1(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 If Len(result) <> 0 Then result = Left(result, Len(result) - 1) LOOKUP_CONCAT1 = Trim(result) Else LOOKUP_CONCAT1 = "" End If End FunctionJust 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