I am trying to search in column A and return what is in column B. My lookup value is in column D and then my table is A&B. For some reason, the vlookup formula isn't working correctly. Maybe vlookup isn't what I need. I need column D to search within column A and return multiple values from Column B separated with a column.
Correction, it might need to be titled Search for text string in a cell and return adjacent values in the same cell separated by a coma.
Last edited by Trnecessary; 10-17-2018 at 10:02 AM. Reason: update
Your "sample" file was a tad large!! If you REALLY want all these in one cell for each row's resuts you will need VBA. This returns one value per cell. I have not processed all the rows as my crappy laptop isn't up to it. But, in principle: drag across and down as far as needed.
=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($A$1:$A$5850)/(ISNUMBER(SEARCH($C1,$A$1:$A$5850))),COLUMNS($A:A))),"")
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Thanks Glenn, I had posted this in the VBA section as well because I didn't know about the size and the amount of work it would need.
Naughty you. Double posting is against the rules. It can lead to time wasting and anmoyance. Is my offering of any use to you?
I tried that formula and it didn't really work but I am open to suggestions on how to get this piece done. Its the only thing hindering me from going forward. I can do a test search and just ctrl+F and take a value from column D and search in column A. Then look to the adjaced cell and just copy and paste. I could do this manually but it would save hours of work for me.
What do you mean by "it didn't really work"? What was arong? Where was it wrong?
I copied that formula and pasted it in Cell C2. It only returned one value (which you had previously stated it would do) and that value was incorrect. Am I doing something incorrectly with the formula?
Did I misread your requirement?
I searched in column A for all cases where the value in C1 was present. the formula then returned the corresponding values from column B, one number per cell going across. if that's not what you wanted, please explain in the same terms to those I have used, what you DO want.
Duplicate threads are not allowed, so please do not open anymore on the same issue. Your other thread has now been closed. Thank you.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Sorry about that Glenn, that was my fault in not explaining what I wanted.
Search in column A for all cases that has the text string from C2. It should return values (multiple if found other times) in the same cell. If possible, I was going to drag this down to continue searching for the adjacent cell and populate the ID numbers associated with them.
Does that make sense or am I just spinning in circles?
Thanks Glenn
STILL not clear. "It should return values"... WHAT values?
Also bear in mind that the data in your sample sheety started in ROW 1 not row 2.
It should return the ID's adjacent to the cell that its found in column A. So if I were to search for NEPHROLITHOTOMY PERCUTANEOUS, it would find that text string in cell A3988 and A3989 and then it would return value in Column B Cell B3988 and B3989.
B3988 value is 10700003987
B3989 value is 10700003988
Those two values would populate in one cell basically.
And my formula did that, albeit in different cells. Look at D1 and E1 in the sheet from Post 2. I suspect the sheet that you posted and the sheet you are working with DIFFER in that yours has a header row, while the posted one has none.
I will post a UDF shortly to do exactly the same, will all numbers in one cell.
Firstly, my sheet from Post 2.... again... showing that it did return the expected result.
Secondly a UDF. I will explain it, ONCE you agree that it is doing what you want IN TJIS SHEET. Enable macros on opening.
The UDF is an array formula. These need to be set with CTRL-SHIFT & ENTER and not just Enter.
GLENN!!! YOU BEAUTIFUL, SMART MAN!!!! You are an EXCEL Genius! Seriously, I can't thank you enough. Sorry again for posting in two areas. I just didn't know which one it would be, formula or VBA.
Even she-who-must-be-obeyed has never called me beautiful!!
Aww gee shucks...
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
Incidentally, if you went for the UDF, do you know what to do to implement it in your own sheet?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks