Search with values from one row in another column and then return value

[Corne89]

Hey guys,
i need help with a topic.
I want, with values from one row, to search for a match in the column of another table. But from all the values in the row, only one needs to be found in the other column. If this is the case, then it should return a specific value, which is the M-ID.

Example:
In sheet "Auswertung" i want to execute the function given above in cell i.e. G5: So when cell E5 is "-" , it should take the values of the row from "Hilfstabelle" (A5) and search for this value(s) in sheet "Import PHI". If there's a match (S12=S12), it should return the ID starting with M. In this case this would be M200 (Position A-11-51-51). If there's no match at all, it should return "-".

Would be cool, if you could help me!! Thank you!

Mappe1 (1).xlsm
	A	B	C	D	E
1	Ort	Bereich	Platz	Ext. VP	Master/Single
2	LAR	A	A-11-51-31	M00073317	M
3	LAR	A	A-11-51-31	S416140010	S
4	LAR	A	A-11-51-31	S416140011	S
5	LAR	A	A-11-51-31	S416140012	S
6	LAR	A	A-11-51-41		M
7	LAR	A	A-11-51-51	M200
8	LAR	A	A-11-51-51	S12
9	LAR	A	A-11-51-61
10	LAR	A	A-11-51-61
11	LAR	A	A-11-51-61
12	LAR	A	A-11-51-61
13	LAR	A	A-11-51-61
14	LAR	A	A-11-51-33	M000	M
15			A-11-51-33	S10
16			A-11-51-100
17
Import PHI

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

Cell Formulas
Range	Formula
B2:B6	B2	=IFERROR(IF(VLOOKUP($A2,'Import Drohne'!$A$1:$B$1000,1,FALSE)=0,"nein","ja"),"nein")
C2:C6	C2	=IF(VLOOKUP($A2,'Import Drohne'!A1:B999,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$999,AGGREGATE(15,6,ROW('Import Drohne'!$B$1:$B$999)/(('Import Drohne'!$A$1:$A$999=$A2)*(LEFT('Import Drohne'!$B$1:$B$999,1)="m")),1)),"-")
G2:G6	G2	=IF(E2="-",VLOOKUP(Hilfstabelle!A2:G2,'Import PHI'!A1:F25,4,FALSE)*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)),"-"),"")
F2:F6	F2	=TXTJN(", ",TRUE,IF(('Import Drohne'!$A$1:$A$999=Auswertung!A2)*("S"=LEFT('Import Drohne'!$B$1:$B$999,1)),'Import Drohne'!$B$1:$B$999,""))
F7	F7	=TXTJN(", ",TRUE,IF(('Import Drohne'!$A$1:$A$999=Auswertung!A7)*("S"=LEFT('Import Drohne'!$B$1:$B$999,1)),'Import Drohne'!B6:B1004,""))
H2:H6	H2	=IF(B2="nein","nein",IF(AND(D2=0,E2=0),"",IF(AND(D2="-",E2="-"),"ja",IF(D2=E2,"ja",IF(D2=G2,"ja","nein")))))
H7	H7	=IF(AND(D7=0,E7=0),"",IF(AND(D7="-",E7="-"),"nein",IF(D7=E7,"ja",IF(D7=G7,"ja","nein"))))
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B:B	Cell Value	contains "nein"	text	NO
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

Mappe1 (1).xlsm
	A	B	C	D	E	F	G	H	I
1
2	S416140010	S416140014
3	S10	S20
4	#VALUE!
5	S12
6	#VALUE!
7	#VALUE!
8
9
10
11
12
13
Hilfstabelle