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

Corne89

New Member
Joined
Apr 21, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEF
1OrtBereichPlatzExt. VPMaster/SingleErläuterung:
2LARAA-11-51-31M00073317M Masterlabel einer Master/Single Struktur
3LARAA-11-51-31S416140010S Singlelabel einer Master/Single Struktur
4LARAA-11-51-31S416140011S Singlelabel einer Master/Single Struktur
5LARAA-11-51-31S10S Singlelabel einer Master/Single Struktur
6LARAA-11-51-41M123455678MGLT Label
7LARAA-11-51-51Leerer Lagerplatz
8LARAA-11-51-51Leerer Lagerplatz
9LARAA-11-51-61S15899
10LARAA-11-51-61S89761
11LARAA-11-51-61M819369
12LARAA-11-51-61S891
13LARAA-11-51-61S10
14LARAA-11-51-33M000M Testlabel1
Import PHI


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


Mappe1.xlsm
ABCDEFGH
1PositionenLagerplatz von Drohne erfasst?Leerer Lagerplatz Drohne?Master-Label (Should) (PHI)Master-Label (Is) (Drohne)Single-Label(s) (ist)Master-Label über Single abgeleitetBestand übereinstimmend? (Drohne/PHI)
2A-11-51-31janeinM00073317-S416140010, S416140011, S10nein
3A-11-51-33jajaM000M5#VALUE!nein
4A-11-51-41#N/A#N/AM123455678-#VALUE!nein
5A-11-51-51janein--S10nein
6A-11-51-61janeinM819369M819369S15899, S89761, S891, S10ja
7Gesamtergebnis#VALUE!ja
Auswertung
Cell Formulas
RangeFormula
B2:B6B2=IF(VLOOKUP($A2,'Import Drohne'!$A$1:$B$1000,1,FALSE)=0,"nein","ja")
C2:C6C2=IF(VLOOKUP($A2,'Import Drohne'!A1:B1000,2,FALSE)=0, "ja","nein")
D2:D6D2=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:E6E2=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:F6F2=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,""))
F7F7=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:H7H2=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
CellConditionCell FormatStop If True
H:HCell Valuecontains "nein"textNO
H:HCell Valuecontains "ja"textNO
H1Cell Valuecontains "ja"textNO
H1Cell Valuecontains "ja"textNO
H1Cell Valuecontains "nein"textNO


 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Forum statistics

Threads
1,148,143
Messages
5,745,043
Members
423,917
Latest member
Frank1931

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
Top