Compare several values in one cell to other cells and return value

[Corne89]

Hey guys,
i need help with a topic.
I want to compare several values in one cell, which are separated with comma with values in another sheet. If one of these values is located in the other sheet, then the ID starting with M should be returned.
Example:
In column F in sheet "Auswertung" I have several values starting with "S" extracted from sheet "Import Drohne". These values starting with "S" are connected to an value starting with M. The values with "M" i get from sheet "Import PHI" and "Import Drohne". These two sheets track the same thing. So if one M-value from one position is in "Import PHI" (column D) it should also be in "Import Drohne" (column E). The same goes for the S-values.
What I want to do is, if one M-Value from "Import Drohne" is missing in column E, I want to check in column G with the values of column F, if one of these values is in "Import PHI" and if yes, then it should return the value starting with M from the same position.

Example:
In "Auswertung" D2, "Import PHI" detected the label M00073317 for the position A-11-31-51. In E2, however this label wasn't detected by "Import Drohne"--> - . In column F, all Labels starting with S from "import Drohne" are listed (F2= S416140010, S416140011, S10). Now I want based on these results in F2, to look up these values in "Import PHI". If only one of these values (i.e. S10) is detected, it should return the value starting with M of this position(i.e. M00073317) in column G.

I hope you understand what I wrote and it would be great if you could help me!!! Thank you!!

Mappe1.xlsm
	A	B	C	D	E	F
1	Ort	Bereich	Platz	Ext. VP	Master/Single	Erläuterung:
2	LAR	A	A-11-51-31	M00073317	M	Masterlabel einer Master/Single Struktur
3	LAR	A	A-11-51-31	S416140010	S	Singlelabel einer Master/Single Struktur
4	LAR	A	A-11-51-31	S416140011	S	Singlelabel einer Master/Single Struktur
5	LAR	A	A-11-51-31	S10	S	Singlelabel einer Master/Single Struktur
6	LAR	A	A-11-51-41	M123455678	M	GLT Label
7	LAR	A	A-11-51-51			Leerer Lagerplatz
8	LAR	A	A-11-51-51			Leerer Lagerplatz
9	LAR	A	A-11-51-61	S15899
10	LAR	A	A-11-51-61	S89761
11	LAR	A	A-11-51-61	M819369
12	LAR	A	A-11-51-61	S891
13	LAR	A	A-11-51-61	S10
14	LAR	A	A-11-51-33	M000	M	Testlabel1
Import PHI

Mappe1.xlsm
	A	B
1	A-11-51-31	N416140
2	A-11-51-31	Q106
3	A-11-51-31	A115171
4
5	A-11-51-31	PA1674905302
6	A-11-51-31	S416140010
7	A-11-51-31	S416140011
8	A-11-51-31	30SL33483DA
9	A-11-51-31	H9999999999
10	A-11-51-31	V12200366C
11	A-11-51-31	S10
12	A-11-51-61	S15899
13	A-11-51-61	S89761
14	A-11-51-61	M819369
15	A-11-51-61	S891
16	A-11-51-61	S10
17	A-11-51-33
18	A-11-51-33	M5
19	A-11-51-51	S10
Import Drohne

Mappe1.xlsm
	A	B	C	D	E	F	G	H
1	Positionen	Lagerplatz von Drohne erfasst?	Leerer Lagerplatz Drohne?	Master-Label (Should) (PHI)	Master-Label (Is) (Drohne)	Single-Label(s) (ist)	Master-Label über Single abgeleitet	Bestand übereinstimmend? (Drohne/PHI)
2	A-11-51-31	ja	nein	M00073317	-	S416140010, S416140011, S10		nein
3	A-11-51-33	ja	ja	M000	M5	#VALUE!		nein
4	A-11-51-41	#N/A	#N/A	M123455678	-	#VALUE!		nein
5	A-11-51-51	ja	nein	-	-	S10		nein
6	A-11-51-61	ja	nein	M819369	M819369	S15899, S89761, S891, S10		ja
7	Gesamtergebnis					#VALUE!		ja
Auswertung

Cell Formulas
Range	Formula
B2:B6	B2	=IF(VLOOKUP($A2,'Import Drohne'!$A$1:$B$1000,1,FALSE)=0,"nein","ja")
C2:C6	C2	=IF(VLOOKUP($A2,'Import Drohne'!A1:B1000,2,FALSE)=0, "ja","nein")
D2:D6	D2	=IFERROR(INDEX('Import PHI'!$D$1:$D$1000,AGGREGATE(15,6,ROW('Import PHI'!$D$1:$D$1000)/(('Import PHI'!$C$1:$C$1000=$A2)*(LEFT('Import PHI'!$D$1:$D$1000,1)="m")),1)),"-")
E2:E6	E2	=IFERROR(INDEX('Import Drohne'!$B$1:$B$1000,AGGREGATE(15,6,ROW('Import Drohne'!$B$1:$B$1000)/(('Import Drohne'!$A$1:$A$1000=$A2)*(LEFT('Import Drohne'!$B$1:$B$1000,1)="m")),1)),"-")
F2:F6	F2	=TXTJN(", ",TRUE,IF(('Import Drohne'!$A$1:$A$1000=Auswertung!A2)*("S"=LEFT('Import Drohne'!$B$1:$B$1000,1)),'Import Drohne'!$B$1:$B$1000,""))
F7	F7	=TXTJN(", ",TRUE,IF(('Import Drohne'!$A$1:$A$1000=Auswertung!A7)*("S"=LEFT('Import Drohne'!$B$1:$B$1000,1)),'Import Drohne'!B6:B1005,""))
H2:H7	H2	=IF(AND(D2="-",E2="-"),"nein",IF(D2=E2,"ja",IF(D2=G2,"ja","nein")))
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H:H	Cell Value	contains "nein"	text	NO
H:H	Cell Value	contains "ja"	text	NO
H1	Cell Value	contains "ja"	text	NO
H1	Cell Value	contains "ja"	text	NO
H1	Cell Value	contains "nein"	text	NO