LOOKUP formula problem

[dotsent]

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):

=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!

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E
1
2
3	abc123	123			456
4	456a	456			123
5	777				878
6	text				911
7	878x911	878	911
8	z122z
9
Main

Cell Formulas
Range	Formula
B3:B6,B8,B7:C7	B3	=TRANSPOSE(FILTER($E$3:$E$6,ISNUMBER(SEARCH($E$3:$E$6,A3)),""))

 

[dotsent]

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?

 

[Fluff]

How many results do you want to return?

 

[dotsent]

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.

 

[Fluff]

You cannot display 5 values if you have data in col E

 

[Fluff]

Makes sure you have nothing in cols B:F & try

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I
1
2
3	abc123	123							456
4	456a	456							123
5	777								878
6	text								911
7	878x911	878	911	78	11				78
8	z122z								11
Main

Cell Formulas
Range	Formula
B3:F8	B3	=IFERROR(INDEX(FILTER($I$3:$I$8,ISNUMBER(SEARCH($I$3:$I$8,A3)),""),SEQUENCE(,5)),"")
Dynamic array formulas.

 

[dotsent]

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).

 

[dotsent]

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.

 

[Fluff]

As far as I'm aware you cannot use Spill ranges in tables.