Vlookup return multiple values in one cell
Hello!
Pls. I tried searching for a solution to my formula problem in this forum regarding vlookup and saw similar formula e.i returning multiple values but it doesn't cater with what I need.
Vlook is returning only the first value but what I need is to return all the multiple return values in 1 cell. I've attached a sample worksheet for reference.
Thank you!
Last edited by rickyilas; 02-16-2010 at 07:43 AM.
Hi,
My recommendation would be for you to really make sure you want this kind of result which denies the use of excel by combining everything into a cell ...
If you are 100% sure of your requirement, designing a comprehensive formula will be a serious headache ... and I think some VBA coding would be easier than a formula ...
HTH
Thanks for the reply. If it's not possible for a formula then I will have to settle for a VBA code. But my problem is I don't know VBA and my concern also is the vlookup value is coming from another file and not in the same worksheet. I just put this together in one sheet for easy reference which I will have to change the source reference later.
Hi again! Anybody? Is there a solution to this problem? Pls. let me know if there's clarification needed to help analyzing the problem.
If VBA is the option, we'll have to assume there will be 2 workbook involved. One is where the formula is (vlookup) and another file (Data) where the Acct#'s are. I want to look-up each PO and it's corresponding Acct# from the other file and have them in one Cell.
Tnx.
Using your sample file... where the Data sheet is seeming listed in Ascending order first by Col A and then by Col B .... you could apply:
Data!C2: =REPT(C1,A1=A2)&REPT(" / "&B2,B2<>B1)
copied down for all rows
Then
Vlookup!B2: =REPLACE(LOOKUP($A2,Data!$A$2:$A$17,Data!$C$2:$C$17),1,3,"")
copied down for all rows
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Re: Vlookup return multiple values in one cell
Donkeyote! I was hoping to hear from you hehehe...Tnx! Although I guess I have to add another column to the other file but it will get the job done. Thank you! Your solution is tricky but it serves my purpose. Well done!
Thank you for helping me out again and to all valued contributors thank you.
Let me just tag this as solved and remove that sad face.
Pls. how can I tag this as solved and change that sad face in my post, I'm happy now :-)..
Standard info....
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
Hi DoneyOte,
I have found a situation with which this equation needs tweeking.
In the situation where the column entry is new but the value is the same as the value of that entry above.
In the example spreadsheet this would mean AB02 has a value of 456. The equation now returns zero value.
PO# Acct Ref Equation
AA01 123 / 123
AA01 123 / 123
AA01 123 / 123
AA01 456 / 123 / 456
AB02 456
AB02 456
Generally speaking questions should not be asked in the thread of another member, however, on this occasion given you highlight a flaw in the original I think it's ok... so:
=REPT(C1,A1=A2)&REPT(" / "&B2,B2<>B1)
becomes
=REPT(C1,A1=A2)&REPT(" / "&B2,OR(A2<>A1,B2<>B1))
I appear to have crafted something.
=IF(A2=A1,D1,"")&IF((AND(A2<>A1,B2=B1)),B2,"")&IF(B2<>B1," / "&B2,"")
Works but is probably a hack job. I'm newbie.
Let me know your thoughts. Thanks for the help.
DonkeyOte you are awesome.
Whooops did it with an IF statement. REPT does the same just needs some rejigging.
Sorry didnčt refresh and see your responses. Thank you again for all the help.
Feel free to delete my non-sense.
I like VBA solutions to these problems personally, as you can hide all the complexity away. There is a VBA multi vlookup that will do this as an alternative here: http://bals-brain.com/Multi_vlookup.htm
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks