Match Partially Strings Between Cells & Ranges

[excelakos]

Hey everybody!! My apologies about this query. Its something i could not even find a proper title. So i hope my description is on the point. Use of screenshots is also needed, but lets start with tables and xl2bb

Betakos Profit Analyzer.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2		PickCode	Pick		League	1	Entos	X	eKTos	2	1.%	X.%	2.%	Odds Class	Index	Pick	PickCode	From	Till	Stakes Target	Profit Target
3		1	1		ΑΓΓΠ	4,15										1	1	1/1/2015	31/12/2099	5	2
4		2	2		ΑΓΓΠ						0,15					X2	32	1/1/2015	31/12/2099
5		3	X
6		12	12
7		13	1X		League	1	Entos	X	eKTos	2	1.%	X.%	2.%	Odds Class
8		32	X2		ΑΓΓΠ	4,15	A	3,5	B	1,8	0,15	0,25	0,6	ΑΓΓΠ 2,37
9
10
11
Portfolio

Cell Formulas
Range	Formula
Q3:Q4	Q3	=IFERROR(INDEX(t_pickconvertor,MATCH([@Pick],t_pickconvertor[Pick],0),MATCH(t_pickconvertor[[#Headers],[PickCode]],t_pickconvertor[#Headers],0)),"")

Table 11 is where i input my selection criteria from cell e3 till cell n.

then in table 12 i have data in respectively cells
Now i need a way to search and match if the data existing in table 12 also exist in table 11

I need to match like the image i attach

The example is this:
Does data in E8 exists in any cell of column "League" in table 11? Then
Does data in F8 exists in any cell of column "1" in table 11? Then
Does data in G8 exists in any cell of column "Entos" in table 11? And so on
As you can notice i have used an example of table 12 where same data exist in both rows of table 11

Betakos Profit Analyzer.xlsb
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2		League	1	Entos	X	eKTos	2	1.%	X.%	2.%	Odds Class	Index	Pick	PickCode	From	Till
3		ΑΓΓΠ	4,15										1	1	1/1/2015	31/12/2099
4		ΑΓΓΠ						0,15					X2	32	1/1/2015	31/12/2099
5
6
7		League	1	Entos	X	eKTos	2	1.%	X.%	2.%	Odds Class	CountIf
8		ΑΓΓΠ	4,15	A	3,5	B	1,8	0,15	0,25	0,6	ΑΓΓΠ 2,37	2
9
10
Portfolio

Cell Formulas
Range	Formula
Q3:Q4	Q3	=IFERROR(INDEX(t_pickconvertor,MATCH([@Pick],t_pickconvertor[Pick],0),MATCH(t_pickconvertor[[#Headers],[PickCode]],t_pickconvertor[#Headers],0)),"")

so i need a kind of count if in cell O8 to inform that there are 2 matches of data inputs in table 11 for the row of table 12
and in this example this finding is controversial because
If i had only the first row of table 11 i need to get Pick 1 as a result
If i had only the 2nd row of table 11 i need to get Pick X2 as a result
Now i need the count if to inform me that there multiple matches and so I need to sort it manually

The logic would also be like this: Each row in table 11 could become a kind of unique id. In example "ΑΓΓΠ&4,15" could be the id for row 1 and then "ΑΓΓΠ&0,15" the id for row 2.
In table 12 we always have full data entry, so this is a wider id than table's 11.
I need to answer this: Is there any id of table 11 matching any string part of tables 12 ids?
I tried to go with this E3&F3&....but i face many issues.
One of them is this

Betakos Profit Analyzer.xlsb
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2		League	1	Entos	X	eKTos	2	1.%	X.%	2.%	Odds Class	Index	Pick	PickCode	From	Till
3		ΑΓΓΠ	4,15									ΑΓΓΠ4,15	1	1	1/1/2015	31/12/2099
4		ΑΓΓΠ						4,15				ΑΓΓΠ4,15	X2	32	1/1/2015	31/12/2099
5
Portfolio

Cell Formulas
Range	Formula
O3	O3	=[@League]&[@1]
O4	O4	=[@League]&[@[1.%]]
Q3:Q4	Q3	=IFERROR(INDEX(t_pickconvertor,MATCH([@Pick],t_pickconvertor[Pick],0),MATCH(t_pickconvertor[[#Headers],[PickCode]],t_pickconvertor[#Headers],0)),"")

For both rows in table 11 i would get ΑΓΓΠ4,15 but this is not the same, because first 4,15 is for label "1" and the other 4,15 is for label "1.%"

Thank you all just for reading this...

 

[GlennUK]

Put separators in like =[@League]&"|"&[@1]&"|"&[@Entos]&"|"&[@2]&"|"&[@1.%]&"|"&[@X.%]&"|"&[@2.%]&"|"&[@Odds Class]

 

[excelakos]

Oh God help me!! It has to be the most complex task ever came across. Ok Lets use your idea about the "|" separators. I will just focus on what i need the final "outcome" to be.

Betakos Profit Analyzer.xlsb
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1
2		League	1	Home	X	Away	2	1.%	X.%	2.%	Odds Class	Index	Pick	PickCode	From	Till	Stakes Target	Profit Target
3		EPL	4,15									|EPL|4,15|||||||||	1	1	1/1/2015	31/3/2021	5	2
4								0,15				|||||||0,15||||	X	3	1/1/2015	31/12/2099
5		EPL			3,50						2,37	|EPL|||3,5||||||2,37|	Over2,5	26	1/1/2015	31/12/2099
6
7		League	1	Home	X	Away	2	1.%	X.%	2.%	Odds Class	Index	Date	Pick
8		EPL	4,15	Team H	3,5	Team A	1,8	0,15	0,25	0,6	2,37	|EPL|4,15|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37|	28/2/2021	1&X&Over2,5
9
Portfolio

Cell Formulas
Range	Formula
O8,O3:O5	O3	="|"&[@League]&"|"&[@1]&"|"&[@Home]&"|"&[@X]&"|"&[@Away]&"|"&[@2]&"|"&[@[1.%]]&"|"&[@[X.%]]&"|"&[@[2.%]]&"|"&[@[Odds Class]]&"|"
Q3:Q5	Q3	=IFERROR(INDEX(t_pickconvertor,MATCH([@Pick],t_pickconvertor[Pick],0),MATCH(t_pickconvertor[[#Headers],[PickCode]],t_pickconvertor[#Headers],0)),"")

In row 8 "table 12" we have the game we need to get Pick Indicators from "table 11" above.
So the separators create 10 data boxes for each row in table 11==> |EPL| is data box No1 and so on....

Now if the data in each row in table 11 are exactly match with the respectively ones in rows in table 12 then i need a multiple INDEX functions to get me the picks as shown in cell Q8 in table 12
Now in my example, each row in table 11 has an absolute match of data in table's 12 row
This means first row of table 11 has 2 filled data boxes==> |EPL|&|4,15|. These 2 data boxes exist too in table 12 row, in the exact positions 1 & 2
2nd row of table 11 has 1 filled data box ==> |0,15| which is the 7th position. In table 12 position 7 also has |0,15|==> Again exact match
3rd row of table 11 has 3 filled data boxes ==> |EPL|&|3,5|&|2,37| in positions 1, 4, 10. In table 12 the exact same positions has the exact same data.

So each of the 3 rows in table 11 has an exact match in table's 12 row of data. So all 3 Picks are valid and need to be placed in cell Q8.
*Important note. In table 11, columns R & S are date restrictions that must apply according to the match date (cell P8 in table 12)

Example of non exact match.

Betakos Profit Analyzer.xlsb
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1
2		League	1	Home	X	Away	2	1.%	X.%	2.%	Odds Class	Index	Pick	PickCode	From	Till	Stakes Target	Profit Target
3		EPL	4,15									|EPL|4,15|||||||||	1	1	1/1/2015	31/3/2021	5	2
4								0,15				|||||||0,15||||	X	3	1/1/2015	31/12/2099
5		EPL			3,50		2,00				2,37	|EPL|||3,5||2||||2,37|	Over2,5	26	1/1/2015	31/12/2099
6
7		League	1	Home	X	Away	2	1.%	X.%	2.%	Odds Class	Index	Date	Pick
8		EPL	4,15	Team H	3,5	Team A	1,8	0,15	0,25	0,6	2,37	|EPL|4,15|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37|	28/2/2021	1&X&Over2,5
9
10
Portfolio

Cell Formulas
Range	Formula
O8,O3:O5	O3	="|"&[@League]&"|"&[@1]&"|"&[@Home]&"|"&[@X]&"|"&[@Away]&"|"&[@2]&"|"&[@[1.%]]&"|"&[@[X.%]]&"|"&[@[2.%]]&"|"&[@[Odds Class]]&"|"
Q3:Q5	Q3	=IFERROR(INDEX(t_pickconvertor,MATCH([@Pick],t_pickconvertor[Pick],0),MATCH(t_pickconvertor[[#Headers],[PickCode]],t_pickconvertor[#Headers],0)),"")

In above example 3rd row of table 11, is not exact match for the row in table 12 cause data box in position 6 (labeled "2"), has no exact match in row of table 12. So in this case Pick Over2.5 must not show up in cell Q8

Put separators in like =[@League]&"|"&[@1]&"|"&[@Entos]&"|"&[@2]&"|"&[@1.%]&"|"&[@X.%]&"|"&[@2.%]&"|"&[@Odds Class]

 

[GlennUK]

Your requirement is difficult to understand ... you seem to be wanting to do a match per field/content from one table to the other, but all in one formula, which I'm not sure is possible. What version of Excel are you trying to do this in?

 

[excelakos]

Your requirement is difficult to understand ... you seem to be wanting to do a match per field/content from one table to the other, but all in one formula, which I'm not sure is possible. What version of Excel are you trying to do this in?

2013 version
Ok lets talk about it like this. Can Excel spot exact text strings of a larger txt into smaller ones?
|EPL|4,15|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37| is a full index

|EPL|4,15||||||||| is a partial index.

Can Excel tell us that "|EPL|4,15|||||||||" is an exact substring of "|EPL|4,15|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37|" ?
And the reason "|EPL|4,15|||||||||" is an exact partial string of "|EPL|4,15|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37|"

is because EPL and 4,15 are in the exact same spots in our example spots 1&2

In example string |EPL|||4,15||||||| IS NOT an exact substring of "|EPL|4,15|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37|"
because |4,15| is in spot 2 in the full index , but in spot 4 in the substring.


|1|2|3|4|5|6|7|8|9|10| is the structure of the strings

 

[GlennUK]

Look at this:

Book3 (version 1).xlsb
	A	B	C	D
1	|EPL|4,15|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37	|EPL|4,15||||||||	|EPL|4,15|*|*|*|*|*|*|*|*	TRUE
2	|EPL|4,15|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37	|EPL||4,15||||||||	|EPL|*|4,15|*|*|*|*|*|*|*|*	FALSE
Sheet8

Cell Formulas
Range	Formula
C1:C2	C1	=SUBSTITUTE(SUBSTITUTE(B1,"||","|*|"),"||","|*|")&"*"
D1:D2	D1	=NOT(ISERROR(SEARCH(C1,A1)))

 

[excelakos]

Dear

Look at this:

Book3 (version 1).xlsb
	A	B	C	D
1	|EPL|4,15|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37	|EPL|4,15||||||||	|EPL|4,15|*|*|*|*|*|*|*|*	TRUE
2	|EPL|4,15|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37	|EPL||4,15||||||||	|EPL|*|4,15|*|*|*|*|*|*|*|*	FALSE
Sheet8

Cell Formulas
Range	Formula
C1:C2	C1	=SUBSTITUTE(SUBSTITUTE(B1,"||","|*|"),"||","|*|")&"*"
D1:D2	D1	=NOT(ISERROR(SEARCH(C1,A1)))

Glenn that was a good starting point! But there is still some work to be done (if possible).
We have 2 tables as below

Book1.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2	League	HomeOdds	HomeTeam	DrawOdds	AwayTeam	AwayOdds	Home%	Draw%	Away%	Class	Id	SUBST	TF	Cells	Pick		Match	Cell	Picks
3	1	2,00	3,0	4,00	5,0	6,00	7,00	8,00	9,00	10,00	|1|2|3|4|5|6|7|8|9|10|	|1|2|3|4|5|6|7|8|9|10|	FALSE				|EPL|4,15|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37|	$Q$3	1&Over2,5
4	EPL	4,15									|EPL|4,15|||||||||	|EPL|4,15|*|*|*|*|*|*|*|*|	TRUE	$Q$3	1		|EPL|4,15|Team H|3,5|Team A|1,5|0,15|0,25|0,6|2,3|	$Q$4
5	EPL					1,80					|EPL|||||1,8|||||	|EPL|*|*|*|*|1,8|*|*|*|*|	TRUE	$Q$3	Over2,5		|EPL|4,8|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37|	$Q$5
Sample

Cell Formulas
Range	Formula
K3:K5	K3	="|"&[League]&"|"&[HomeOdds]&"|"&[HomeTeam]&"|"&[DrawOdds]&"|"&[AwayTeam]&"|"&[AwayOdds]&"|"&[Home%]&"|"&[Draw%]&"|"&[Away%]&"|"&[Class]&"|"
L3:L5	L3	=SUBSTITUTE(SUBSTITUTE([@Id],"||","|*|"),"||","|*|")
M3:M5	M3	=NOT(ISERROR(SEARCH([@SUBST],schedule[Match])))
R3:R5	R3	=CELL("address",[@Match])
S3:S5	S3	=MultipleLookupNoRept([@Cell],picksid[[Cells]:[Pick]],2)

Now lest start with scenario 1 which is the simplest, in which a "match" from table "schedule" has multiple matchups in table "picksid"
In the above example first match meets the criteria in both id2 & id3. Now if beside the TRUE, FALSE we get in column "M", we could get the cell address of the matching, as i have mark it manually in the example, i have found the solution to get both picks for the match in cell S3 with the help of the below code

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
'Updateby Extendoffice
    Dim xDic As New Dictionary
    Dim xRows As Long
    Dim xStr As String
    Dim i As Long
    On Error Resume Next
    xRows = LookupRange.Rows.Count
    For i = 1 To xRows
        If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
            xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
        End If
    Next
    xStr = ""
    MultipleLookupNoRept = xStr
    If xDic.Count > 0 Then
        For i = 0 To xDic.Count - 1
            xStr = xStr & xDic.Keys(i) & "&"
        Next
        MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
    End If
End Function

So this is the example where games match multiple pickids=> 1 game matches 2 ids in the above example.

Now the second scenario is the tougher one. It is the scenario where a pickid matches multiple games.

This is a table example of what i would wish to get as a final outcome. Note that the results are hand written (yellow font) for explaining purposes.

Book1.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2	League	HomeOdds	HomeTeam	DrawOdds	AwayTeam	AwayOdds	Home%	Draw%	Away%	Class	Id	SUBST	TF	Cells	Pick		Match	Cell	Picks
3	1	2,00	3,0	4,00	5,0	6,00	7,00	8,00	9,00	10,00	|1|2|3|4|5|6|7|8|9|10|	|1|2|3|4|5|6|7|8|9|10|	FALSE				|EPL|4,15|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37|	$Q$3	1&Over2,5
4	EPL	4,15									|EPL|4,15|||||||||	|EPL|4,15|*|*|*|*|*|*|*|*|	TRUE	$Q$3,$Q$4	1		|EPL|4,15|Team H|3,5|Team A|1,5|0,15|0,25|0,6|2,3|	$Q$4	1
5	EPL					1,80					|EPL|||||1,8|||||	|EPL|*|*|*|*|1,8|*|*|*|*|	TRUE	$Q$3,$Q$5	Over2,5		|EPL|4,8|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37|	$Q$5	Over2,5
Sample (2)

Cell Formulas
Range	Formula
K3:K5	K3	="|"&[League]&"|"&[HomeOdds]&"|"&[HomeTeam]&"|"&[DrawOdds]&"|"&[AwayTeam]&"|"&[AwayOdds]&"|"&[Home%]&"|"&[Draw%]&"|"&[Away%]&"|"&[Class]&"|"
L3:L5	L3	=SUBSTITUTE(SUBSTITUTE([@Id],"||","|*|"),"||","|*|")
M3:M5	M3	=NOT(ISERROR(SEARCH([@SUBST],schedule8[Match])))
R3:R5	R3	=CELL("address",[@Match])

Any work arounds for this?

Thanks to anyone in advance