LOOKUP formula problem

dotsent

Board Regular
Joined
Feb 28, 2016
Messages
85
Office Version
  1. 365
Platform
  1. Windows
Sorry for the vague title. Trying to get this done in a nice way, but feels more difficult than it looks. I have two tables - Strings and Data. Starting off with data in grey columns and I need to extract verified data to green (already filled as a manual "to-be" example).

Values in table Data should be looked for in Strings table, and extracted to columns Match1 and Match2. I surely don't want to over-complicate formulas, but the less helper columns, the better. I could use additional helper column and run wildcard-XLOOKUP such as (please see pink folumn F):

Code:
=XLOOKUP("*"&E3&"*",Strings[String],Strings[String],"",2,1)

+ then add another XLOOKUP in column B to grab the E-column value through the pink helper column. However I'm not sure how to handle case such as row no 7, where there are more than 1 match to fetch.
Should also 2 helper columns be used?

Or is there a nicer, more tidy way to do this?
I'm running Office365, so newer Excel tools should be available.

Thanks a lot!


matches.jpg
 

Attachments

  • matches.jpg
    matches.jpg
    30.2 KB · Views: 5

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
How about

+Fluff New.xlsm
ABCDE
1
2
3abc123123456
4456a456123
5777 878
6text 911
7878x911878911
8z122z 
9
Main
Cell Formulas
RangeFormula
B3:B6,B8,B7:C7B3=TRANSPOSE(FILTER($E$3:$E$6,ISNUMBER(SEARCH($E$3:$E$6,A3)),""))
 
Upvote 0
Thanks Fluff, gave it a try. I did change $E$3:$E$6 into Data[Data] range, since the range needs to be dynamic. Unfortunately I got SPILL! error for 7th row (it worked for others). Same happened with fixed ranges.

Just an add-on: I created a simple example with 2 matching columns, but in reality there is up to 5.
E.g. regardless how many MATCH-columns would be populated, the number of columns in Strings-table should be fixed. That even applies if there are more matches to be found as there are MATCH-columns. Might TRANSPOSE formula conflict with this restriction?
 
Upvote 0
How many results do you want to return?
 
Upvote 0
Up to 5 as of now.
Can increase slightly in the future, but I assume whatever the solution, I hope to be capable to amend it accordingly.
 
Upvote 0
You cannot display 5 values if you have data in col E
 
Upvote 0
Makes sure you have nothing in cols B:F & try
+Fluff New.xlsm
ABCDEFGHI
1
2
3abc123123456
4456a456123
5777 878
6text 911
7878x911878911781178
8z122z 11
Main
Cell Formulas
RangeFormula
B3:F8B3=IFERROR(INDEX(FILTER($I$3:$I$8,ISNUMBER(SEARCH($I$3:$I$8,A3)),""),SEQUENCE(,5)),"")
Dynamic array formulas.
 
Upvote 0
Oh, I see what you mean. Sorry, that's a mockup example.
Tables are actually in different sheets, there is 5 columns of room available. Just as in the example there is 2 (MATCH1 + MATCH2).
 
Upvote 0
Fluff, I kept having the SPILL! error. But then understood it doesn't really work that well inside a Table, right? Once I converted table to named range, it clicked for me right away. Unfortunately there is some additional functionality that require Table-formatting.

Dynamic array formulas (e.g. FILTER, SEQUENCE) in general don't work that well inside Tables, right?

Frankly my real underlying problem got solved - I ditched the idea of trying to extract values from the string and switched to analyze string data as a whole. My code runs slower, but quality of the outcome is better. But no doubt posting this and trying to figure this thing out helped in getting to that point.
 
Last edited:
Upvote 0
As far as I'm aware you cannot use Spill ranges in tables.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top