LinkBack URL
About LinkBacks
Hi All,
I have a need to return all partial matches from strings held in a single column. The returned results will be listed in seperate columns. I have attached a sample sheet with expected results.
Any help will be greatly appreciated.
Hi All,
I have a need to return all partial matches from strings held in a single column. The returned results will be listed in seperate columns. I have attached a sample sheet with expected results.
Any help will be greatly appreciated.
Remove ALL expected resuts from C5 to E10, then in C5 followed by ENTER:
=IFNA(DROP(REDUCE("",B5:B10,LAMBDA(x,y,VSTACK(x,TOROW(FILTER(H5:H11,ISNUMBER(SEARCH(G5:G11,y))))))),1),"")
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. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
This should work for all version of Excel (from 2010+)
C5:
Please Login or Register to view this content.
Quang PT
Whilst both answers work perfectly, so thank you both very much, I failed to note that I have instances of similar partial matches that will conflict. E.g. RF vs RFj.
I have adjusted the attached example sheet to reflect an example of this.
Also I would much prefer a solution that does not use a SPILL formula.
Thank you again
That was indeed a VERY significant ommission!
I'll have another look ...
Try this instead:
=IFNA(DROP(REDUCE("",B5:B11,LAMBDA(x,y,VSTACK(x,TOROW(XLOOKUP(TEXTSPLIT(y," "),$G$5:$G$12,$H$5:$H$12),2)))),1),"")
Why?Also I would much prefer a solution that does not use a SPILL formula.
In C5 copied down:
=TOROW(XLOOKUP(TEXTSPLIT(B5," "),$G$5:$G$12,$H$5:$H$12),2)
BUT it still needs to spill to the right ...
@AliGW you're a superstar. This works exactly as expected, thank you very much. Rep added and thread marked as solved.
If anyone ever finds a non-SPILL solution to this I would also love to see it.
Why are you so anti-spill formulae??? We really need to know. Is this a compatibility issue? Because if it is, then 365 did not ought to be the version in your profile. Please let us know.
My experience with using formulas that SPILL within data formatted as a table has not been good. I often get #SPILL or #CALC issues. In general I'm not experiened with SPILL functions, and probably something I need to brush up on
OK - in that case, you always need to state clearly that you want to use the formulae suggested in tables, because SPILL formulae won't work in them. Please make sure that you do this in post #1 in any further threads. Thanks.
Tr this copied across and down:
=IFERROR(INDEX(TOROW(XLOOKUP(TEXTSPLIT($B5," "),$G$5:$G$12,$H$5:$H$12),2),,COLUMNS($C4:C4)),"")
Cell C5 formula , Drag down
Formula:
Please Login or Register to view this content.
Just add " " before and after string, then seach:Originally Posted by 77highland
C5:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks