Very complex - Vlookup or Index/Match etc...

[DarkSmile]

Hi,

FILE : https://we.tl/t-F8T753nTpu

I need some help to lookup information, its hard to explain so I'm gonna try my best.
I have some clients, I lookup in an other table there "normen", these "normen" I sum up vertically

I than use a vlookup to go in an other sheet and where necessary give me there sectors.

What I wanna achieve now is to check which of my auditors is qualified for the full sector or partially and return "fully qualified" or "partially qualified"
How to achieve this :

In sheet "kwalificaties" you need to go search for the "norm", so for example "IFS", then underneath you have all the sectors summed up per auditor.
So short: lookup the norm + auditor => match in sheet "Planningstabel" the sector (E) with what's in sheet "kwalificaties" and return if its a full, partially or no match.

Example :

Customer test1 has IFS with following sectors =

01 Red and white meat, poultry and meat products , 07 Combined products , B Pasteurisation with the purpose to reduce food safety hazards (and UHT p… , D System, treatments to maintain product integrity and or safety , E Systems, treatments to prevent product contamination , F Any other manipulation, treatment, rocessing not being listed in A,B,C,D,E

now I need to go to sheet "kwalificaties" en match this ^ with the auditor's

Result will be:
Name1 : fully qualified (has the required sectors and even more)
Name2: idem
Name15: is not quallified
Name18: partially (has some of the requirements but not all)

The above should be done with all the norms, not all of them have requirements, some have an X (this means = fully qualified)
So we check all the norms that are summed up, and then in sheet "planningstabel" next to there names. there should come qualified, not qualified, partially qualified.
Fully qualified means they are ok to do all the norms.

I join you a file so that it's hopefully a little easier to understand, feel free to add extra columns if needed.

Thank you very much in advance!

David

 

[awoohaw]

is it possible you can post this using the xl2bb add in.
people that want to help you with this will have to do a lot of typing to simulate your data.

 

[DarkSmile]

is it possible you can post this using the xl2bb add in.
people that want to help you with this will have to do a lot of typing to simulate your data.

I added the file so you can download it, but here is the xl2bb data

MrExcel - File.xlsx
	A	B	C	D	E	F	G
1	Klantnaam	Klantnaam2	Normen	Normen2	Sector	Gekwalificeerd?	Auditor
2	test1	test1	IFS v7, ACS-018, ACS-019 Fenavian, BePork Slachthuis, BePork Uitsnijderij, Febev + Slachthuis voor varkens, Febev + Uitsnijderij voor varkens, BVI/MPT Slachthuis & Uitsnijderij, Colruyt, Export - China, Export Zuid-Korea Slachthuis, Export Zuid-Korea Uitsnijderij, Export Zuid-Korea Verwerking	IFS v7	01 Red and white meat, poultry and meat products , 07 Combined products , B Pasteurisation with the purpose to reduce food safety hazards (and UHT p… , D System, treatments to maintain product integrity and or safety , E Systems, treatments to prevent product contamination , F Any other manipulation, treatment, rocessing not being listed in A,B,C,D,E		Name1
3		test1		ACS-018	0		Name2
4		test1		ACS-019 Fenavian	N/A		Name3
5		test1		BePork Slachthuis	N/A		Name4
6		test1		BePork Uitsnijderij	N/A		Name5
7		test1		Febev + Slachthuis voor varkens	N/A		Name6
8		test1		Febev + Uitsnijderij voor varkens	N/A		Name7
9		test1		BVI/MPT Slachthuis & Uitsnijderij	N/A		Name8
10		test1		Colruyt	N/A		Name9
11		test1		Export - China	N/A		Name10
12		test1		Export Zuid-Korea Slachthuis	N/A		Name11
13		test1		Export Zuid-Korea Uitsnijderij	N/A		Name12
14		test1		Export Zuid-Korea Verwerking	N/A		Name13
15		test1					Name14
16		test1					Name15
17		test1					Name16
18		test1					Name17
19		test1					Name18
20		test1					Name19
21		test1					Name20
22		test1					Reserve
23		test1					Reserve
24		test1					Reserve
25		test1					Reserve
Planningstabel

Cell Formulas
Range	Formula
D2:D14	D2	=FILTER('Klant - sector'!G:G,'Klant - sector'!B:B=Planningstabel!A2)
E2	E2	=IFERROR(VLOOKUP(A2&D2,'Klant - sector'!A:J,8,FALSE),"N/A")
E3:E14	E3	=IFERROR(VLOOKUP(A2&D3,'Klant - sector'!A:J,8,FALSE),"N/A")
B2	B2	=A2
C2	C2	=TEXTJOIN(", ",TRUE,IF(A2=klant_sector[Name],klant_sector[standard],""))
B3	B3	=A2
B4	B4	=A2
B5	B5	=A2
B6	B6	=A2
B7	B7	=A2
B8	B8	=A2
B9	B9	=A2
B10	B10	=A2
B11	B11	=A2
B12	B12	=A2
B13	B13	=A2
B14	B14	=A2
B15	B15	=A2
B16	B16	=A2
B17	B17	=A2
B18	B18	=A2
B19	B19	=A2
B20	B20	=A2
B21	B21	=A2
B22	B22	=A2
B23	B23	=A2
B24	B24	=A2
B25	B25	=A2
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
'Klant - sector'!ExternalData_1	='Klant - sector'!$B$1:$J$14	D2:E2, E3:E14

 

[DarkSmile]

Bump, still need help pls

 

[DarkSmile]

Bump, still need help pls

I have made I change, not sure if it will be helpfull:

I added a formula to return me the column number of the "norm", so now I need to say, search in "Kwalificaties" the name of the person, go to column x and return if it's empty or not

2023 planningstool workfile David.xlsx
	D	E	F	G
11592	IFS v7	01 Red and white meat, poultry and meat products , 07 Combined products , B Pasteurisation with the purpose to reduce food safety hazards (and UHT p… , D System, treatments to maintain product integrity and or safety , E Systems, treatments to prevent product contamination , F Any other manipulation, treatment, rocessing not being listed in A,B,C,D,E	64
11593	ACS-018	0	16
11594	ACS-019 Fenavian	N/A	17
11595	BePork Slachthuis	N/A	29
11596	BePork Uitsnijderij	N/A	30
11597	Febev + Slachthuis voor varkens	N/A	43
11598	Febev + Uitsnijderij voor varkens	N/A	45
11599	BVI/MPT Slachthuis & Uitsnijderij	N/A	33
11600	Colruyt	N/A	35
11601	Export - China	N/A	36
11602	Export Zuid-Korea Slachthuis	N/A	38
11603	Export Zuid-Korea Uitsnijderij	N/A	39
11604	Export Zuid-Korea Verwerking	N/A	40
11605			N/A
11606			N/A
11607			N/A
11608			N/A
11609			N/A
11610			N/A
11611			N/A
11612			N/A
11613			N/A
11614			N/A
11615			N/A
Planningstabel

Cell Formulas
Range	Formula
D11592:D11604	D11592	=FILTER('Klant - sector'!G:G,'Klant - sector'!B:B=Planningstabel!A11592)
E11592	E11592	=IFERROR(VLOOKUP(A11592&D11592,'Klant - sector'!A:J,8,FALSE),"N/A")
E11593:E11604	E11593	=IFERROR(VLOOKUP(A11592&D11593,'Klant - sector'!A:J,8,FALSE),"N/A")
F11592:F11615	F11592	=IFERROR(MATCH(D11592,Kwalificaties!$1:$1),"N/A")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
'Klant - sector'!ExternalData_1	='Klant - sector'!$B$1:$J$1282	D11592:E11592, E11593:E11604

 

[awoohaw]

can you also post the potions of the worksheets (Kwalificates, and Klant-Sector) with lookup tables?
Also, please tell what cell formula is the problem, and what you expect the answer to be?

I cannot download your workbook.

 

[DarkSmile]

can you also post the potions of the worksheets (Kwalificates, and Klant-Sector) with lookup tables?
Also, please tell what cell formula is the problem, and what you expect the answer to be?

I cannot download your workbook.

So here is (hopefully) all you need;

Sheet: Planningstabel -> in this sheet needs to come the final "answer"

MrExcel - File.xlsx
	A	B	C	D	E	F	G	H
1	Klantnaam	Klantnaam2	Normen	Normen2	Sector	Column	Gekwalificeerd?	Auditor
2	test1	test1	IFS v7, ACS-018, ACS-019 Fenavian, BePork Slachthuis, BePork Uitsnijderij, Febev + Slachthuis voor varkens, Febev + Uitsnijderij voor varkens, BVI/MPT Slachthuis & Uitsnijderij, Colruyt, Export - China, Export Zuid-Korea Slachthuis, Export Zuid-Korea Uitsnijderij, Export Zuid-Korea Verwerking	IFS v7	01 Red and white meat, poultry and meat products , 07 Combined products , B Pasteurisation with the purpose to reduce food safety hazards (and UHT p… , D System, treatments to maintain product integrity and or safety , E Systems, treatments to prevent product contamination , F Any other manipulation, treatment, rocessing not being listed in A,B,C,D,E	64		Name1
3		test1		ACS-018	0	16		Name2
4		test1		ACS-019 Fenavian	N/A	17		Name3
5		test1		BePork Slachthuis	N/A	29		Name4
6		test1		BePork Uitsnijderij	N/A	30		Name5
7		test1		Febev + Slachthuis voor varkens	N/A	43		Name6
8		test1		Febev + Uitsnijderij voor varkens	N/A	45		Name7
9		test1		BVI/MPT Slachthuis & Uitsnijderij	N/A	33		Name8
10		test1		Colruyt	N/A	35		Name9
11		test1		Export - China	N/A	36		Name10
12		test1		Export Zuid-Korea Slachthuis	N/A	38		Name11
13		test1		Export Zuid-Korea Uitsnijderij	N/A	39		Name12
14		test1		Export Zuid-Korea Verwerking	N/A	40		Name13
15		test1				N/A		Name14
16		test1				N/A		Name15
17		test1				N/A		Name16
18		test1				N/A		Name17
19		test1				N/A		Name18
20		test1				N/A		Name19
21		test1				N/A		Name20
22		test1				N/A		Reserve
23		test1				N/A		Reserve
24		test1				N/A		Reserve
25		test1				N/A		Reserve
Planningstabel

Cell Formulas
Range	Formula
D2:D14	D2	=FILTER('Klant - sector'!G:G,'Klant - sector'!B:B=Planningstabel!A2)
E2	E2	=IFERROR(VLOOKUP(A2&D2,'Klant - sector'!A:J,8,FALSE),"N/A")
E3:E14	E3	=IFERROR(VLOOKUP(A2&D3,'Klant - sector'!A:J,8,FALSE),"N/A")
B2	B2	=A2
C2	C2	=TEXTJOIN(", ",TRUE,IF(A2=klant_sector[Name],klant_sector[standard],""))
B3	B3	=A2
B4	B4	=A2
B5	B5	=A2
B6	B6	=A2
B7	B7	=A2
B8	B8	=A2
B9	B9	=A2
B10	B10	=A2
B11	B11	=A2
B12	B12	=A2
B13	B13	=A2
B14	B14	=A2
B15	B15	=A2
B16	B16	=A2
B17	B17	=A2
B18	B18	=A2
B19	B19	=A2
B20	B20	=A2
B21	B21	=A2
B22	B22	=A2
B23	B23	=A2
B24	B24	=A2
B25	B25	=A2
F2:F25	F2	=IFERROR(MATCH(D2,Kwalificaties!$1:$1),"N/A")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
'Klant - sector'!ExternalData_1	='Klant - sector'!$B$1:$J$14	D2:E2, E3:E14

Sheet: kwalificaties -> from this sheet the formula needs to get the information

MrExcel - File.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV
1	Auditor	ACS-003 Déb Vian	ACS-007 Retail	ACS-023 Horeca	ACS-025 Collectiv	ACS-026 boul distri	ACS-041 Creche	ACS-044 BtoC	ACS-002 Ind Lait	ACS-004 Brasseries	ACS-005 glaces-ijs	ACS-006	ACS-009 Tran/col Lait	ACS-014 Fr et Lég	ACS-017	ACS-018	ACS-019 Fenavian	ACS-020 Meuneries	ACS-022 Choprabisco	ACS-024	ACS-026 boul transfo	ACS-027	ACS-029	ACS-032	ACS-039	Bel-P-Plus	Belplume Slachthuis	Belplume Transport	BePork Slachthuis	BePork Uitsnijderij	BePork Vleesverwerker	BePork welfare	BVI/MPT Slachthuis & Uitsnijderij	BVI/MPT Vleesverwerker	Colruyt	Export - China	Export Zuid-Korea Koelhuis	Export Zuid-Korea Slachthuis	Export Zuid-Korea Uitsnijderij	Export Zuid-Korea Verwerking	Febev + Groothandel	Febev + Slachthuis voor runderen	Febev + Slachthuis voor varkens	Febev + Uitsnijderij voor runderen	Febev + Uitsnijderij voor varkens	GRMS	ISO 22000:2018	SPECIFIC STANDARD	AOECS Module for Gluten Free Foods	ASDA-module	BLK	BRC Agents & Brokers	BRC Packaging	BRC S&D	BRC v9	FSMA Module Preventive Controls Preparedness	FSSC 22000 v5	GFCP	Head Office	IFS Broker	IFS Cash & Carry v2	IFS Global Markets Food	IFS L	IFS v7	IFS Wholesale v2	Suppliers FOOD	Aldi Australië	Barry Callebaut	Carrefour	Delhaize - PIA	Lidl	Lidl - Animal welfare	Puratos	Taste & Welfare audits - Belgian Porc Group
2	Name1								x					x	x		x			x					x								x	x	x	x	x	x	x	x										x					1 Raw Red Meat , 5 Fruits, vegetables and nuts, 6 Prepared fruits, vegetables and nuts , 8 Cooked meat/fish products , 10 Ready meals and sandwiches, ready to eat desserts , 11 Low/high acid in cans/glass , 18 Oils and fats							1 Raw Red Meat , 2 Raw Poultry , 4 Raw fish products and preparations , 5 Fruits, vegetables and nuts , 7 Dairy, liquid eggs , 9 Raw cured and/or fermented meat and fish		01 Red and white meat, poultry and meat products , 05 Fruit and vegetables , 07 Combined products , 09 Oils and fats , B Pasteurisation with the purpose to reduce food safety hazards (and UHT process) , C Processed products: treatement with purpose to modify product and/or extend shelf life and/or reduce food safety hazards by preservation techniques and other processing techniques. , D System, treatments to maintain product integrity and or safety , E Systems, treatments to prevent product contamination , F Any other manipulation, treatment, rocessing not being listed in A,B,C,D,E
3	Name2								x	x	x			x	x		x		x		x		x		x								x	x	x	x	x	x	x	x									x	x			2 Paper Making , 4 Rigid Plastics , 5 Flexible Plastics , 7 Print Processes		5 Fruits, vegetables and nuts, 6 Prepared fruits, vegetables and nuts , 7 Dairy, liquid eggs , 8 Cooked meat/fish products , 9 Raw cured and/or fermented meat and fish , 10 Ready meals and sandwiches, ready to eat desserts , 11 Low/high acid in cans/glass , 12 Beverages , 13 Alcoholic drinks and fermented/brewed products , 14 Bakery , 15 Dried foods and ingredients , 16 Confectionery , 17 Cereals and snacks , 18 Oils and fats		CIa Meat, and meat products, shell egg , CIIa Unprocessed F&V , CIIb Processed F&V , CIII Combined Products (RTE/RTH) , CIVa Ambient products (pasteurized/sterilized) , CIVb Bakery, snacks and breakfast cereals , CIVc Confectionary , CIVd Alcoholic and non-alcoholic drinks , CIVe Oils & fats , CIVf Dried goods , FIa Retail/wholesale with food processing , FIb Retail/wholesale without food processing , GI Transport and storage for perishable food and feed , GII Transport and storage of non perishable food, feed and packing material , K Production of (Bio) Chemicals , Ia Paper , Ib Plastic	x				1 Raw Red Meat , 2 Raw Poultry , 3 Raw prepared products (meat and vegetarian) , 4 Raw fish products and preparations , 5 Fruits, vegetables and nuts , 6 Prepared fruits, vegetables and nuts. , 7 Dairy, liquid eggs , 8 Cooked meat/fish products , 9 Raw cured and/or fermented meat and fish , 10 Ready meals and sandwiches, ready to eat desserts		01 Red and white meat, poultry and meat products , 03 Egg and egg products , 04 Dairy products , 05 Fruit and vegetables , 06 Grain products, cereals, industrial bakery and pastry, confectionary, snacks , 07 Combined products , 08 Beverages , 09 Oils and fats , 10 Dry goods, other ingredients and supllements , B Pasteurisation with the purpose to reduce food safety hazards (and UHT process) , C Processed products: treatement with purpose to modify product and/or extend shelf life and/or reduce food safety hazards by preservation techniques and other processing techniques. , D System, treatments to maintain product integrity and or safety , E Systems, treatments to prevent product contamination , F Any other manipulation, treatment, rocessing not being listed in A,B,C,D,E
4	Name3															x													x	x		x	x	x	x	x	x	x	x	x		x	x	x	x
5	Name4																																x	x	x	x	x	x	x	x
6	Name5									x		x			x	x	x								x		x	x	x	x	x	x	x	x	x	x	x	x	x	x		x	x	x	x	x			x			x			1 Raw Red Meat , 2 Raw Poultry , 3 Raw prepared products (meat and vegetarian) , 5 Fruits, vegetables and nuts , 6 Prepared fruits, vegetables and nuts , 8 Cooked meat/fish products , 9 Raw cured and/or fermented meat and fish			x				1 Raw Red Meat , 2 Raw Poultry , 5 Fruits, vegetables and nuts , 7 Dairy, liquid eggs , 8 Cooked meat/fish products , 11 Low/high acid in cans/glass		01 Red and white meat, poultry and meat products , 02 Fish and fish products , 05 Fruit and vegetables , 07 Combined products , 08 Beverages , 11 Pet food , B Pasteurisation with the purpose to reduce food safety hazards (and UHT process) , C Processed products: treatement with purpose to modify product and/or extend shelf life and/or reduce food safety hazards by preservation techniques and other processing techniques. , D System, treatments to maintain product integrity and or safety , E Systems, treatments to prevent product contamination , F Any other manipulation, treatment, rocessing not being listed in A,B,C,D,E
7	Name6								x			x		x	x	x	x		x	x	x				x		x		x	x	x	x	x	x	x	x	x	x	x	x		x	x	x	x					x				1. Chilled and frozen food , 2. Ambient food , 3. Packaging and packing materials , 4. Consumer products	1 Raw Red Meat , 2 Raw Poultry , 3 Raw prepared products (meat and vegetarian) , 4 Raw fish products and preparations , 5 Fruits, vegetables and nuts , 6 Prepared fruits, vegetables and nuts , 7 Dairy, liquid eggs , 8 Cooked meat/fish products , 9 Raw cured and/or fermented meat and fish , 10 Ready meals and sandwiches, ready to eat desserts , 11 Low/high acid in cans/glass , 14 Bakery , 15 Dried foods and ingredients , 16 Confectionery , 18 Oils and fats		CIa Meat, and meat products, shell egg , CIc Dairy, liquid and dried egg , CIIa Unprocessed F&V , CIIb Processed F&V , CIII Combined Products (RTE/RTH) , CIVa Ambient products (pasteurized/sterilized) , CIVb Bakery, snacks and breakfast cereals , CIVe Oils & fats , CIVf Dried goods , GI Transport and storage for perishable food and feed , GII Transport and storage of non perishable food, feed and packing material , K Production of (Bio) Chemicals					1 Raw Red Meat , 5 Fruits, vegetables and nuts , 6 Prepared fruits, vegetables and nuts. , 7 Dairy, liquid eggs		01 Red and white meat, poultry and meat products , 05 Fruit and vegetables , 06 Grain products, cereals, industrial bakery and pastry, confectionary, snacks , 07 Combined products , B Pasteurisation with the purpose to reduce food safety hazards (and UHT process) , C Processed products: treatement with purpose to modify product and/or extend shelf life and/or reduce food safety hazards by preservation techniques and other processing techniques. , D System, treatments to maintain product integrity and or safety , E Systems, treatments to prevent product contamination , F Any other manipulation, treatment, rocessing not being listed in A,B,C,D,E
8	Name7	x	x											x	x		x							x	x								x	x	x	x	x	x	x	x						x									1 Raw Red Meat , 2 Raw Poultry , 3 Raw prepared products (meat and vegetarian) , 4 Raw fish products and preparations , 8 Cooked meat/fish products , 9 Raw cured and/or fermented meat and fish , 10 Ready meals and sandwiches, ready to eat desserts		CIa Meat, and meat products, shell egg , CIb Fish and fish products , CIII Combined Products (RTE/RTH) , FIa Retail/wholesale with food processing , FIb Retail/wholesale without food processing , GI Transport and storage for perishable food and feed , GII Transport and storage of non perishable food, feed and packing material			1.1 Red and white meat, poultry and meat products , 1.2 Fish and fish products , 1.3 Egg and egg products , 1.4 Dairy Products , 1.5 Fruit and vegetables , 1.6 Grain products, cereals, industrial bakery and pastry, confectionary, snacks , 1.7 Combined Products , 1.8 Beverages , 1.9 Oils and fats , 1.10 Dry products, other ingredients and supplements , 3.1 Flexible packaging , 3.2 Rigid plastic , 3.3 Paper , 3.4 Metal , 3.5 Glass , 3.6 Other natural materials		1 Raw Red Meat , 5 Fruits, vegetables and nuts , 7 Dairy, liquid eggs	x	01 Red and white meat, poultry and meat products , 05 Fruit and vegetables , 07 Combined products , B Pasteurisation with the purpose to reduce food safety hazards (and UHT process) , C Processed products: treatement with purpose to modify product and/or extend shelf life and/or reduce food safety hazards by preservation techniques and other processing techniques. , D System, treatments to maintain product integrity and or safety , E Systems, treatments to prevent product contamination , F Any other manipulation, treatment, rocessing not being listed in A,B,C,D,E
9	Name8								x		x		x	x	x		x	x	x		x												x	x	x	x	x	x	x	x										x			2 Paper Making , 3 Metal Forming , 4 Rigid Plastics , 5 Flexible Plastics , 7 Print Processes , VM Traded Goods	1. Chilled and frozen food , 2. Ambient food , 3. Packaging and packing materials , 4. Consumer products	4 Raw fish products and preparations , 5 Fruits, vegetables and nuts , 6 Prepared fruits, vegetables and nuts , 7 Dairy, liquid eggs , 8 Cooked meat/fish products , 9 Raw cured and/or fermented meat and fish , 10 Ready meals and sandwiches, ready to eat desserts , 11 Low/high acid in cans/glass , 12 Beverages , 13 Alcoholic drinks and fermented/brewed products , 14 Bakery , 15 Dried foods and ingredients , 16 Confectionery		CIa Meat, and meat products, shell egg , CIc Dairy, liquid and dried egg , CIIa Unprocessed F&V , CIIb Processed F&V , CIII Combined Products (RTE/RTH) , CIVa Ambient products (pasteurized/sterilized) , CIVb Bakery, snacks and breakfast cereals , CIVc Confectionary , CIVf Dried goods , GI Transport and storage for perishable food and feed , GII Transport and storage of non perishable food, feed and packing material , FIa Retail/wholesale with food processing , FIb Retail/wholesale without food processing , K Production of (Bio) Chemicals , Ia Paper , Ib Plastic , Id Metal			1.1 Red and white meat, poultry and meat products , 1.2 Fish and fish products , 1.4 Dairy Products , 1.5 Fruit and vegetables , 1.6 Grain products, cereals, industrial bakery and pastry, confectionary, snacks , 1.7 Combined Products , 1.8 Beverages , 1.9 Oils and fats , 1.10 Dry products, other ingredients and supplements , 3.1 Flexible packaging , 3.2 Rigid plastic , 3.3 Paper , 3.4 Metal , 3.5 Glass , 3.6 Other natural materials		1 Raw Red Meat , 4 Raw fish products and preparations , 5 Fruits, vegetables and nuts , 6 Prepared fruits, vegetables and nuts. , 7 Dairy, liquid eggs , 8 Cooked meat/fish products	x	01 Red and white meat, poultry and meat products , 04 Dairy products , 05 Fruit and vegetables , 06 Grain products, cereals, industrial bakery and pastry, confectionary, snacks , 07 Combined products , 08 Beverages , A Sterilisation (in final packaging) with the purpose to destroy pathogens , B Pasteurisation with the purpose to reduce food safety hazards (and UHT process) , C Processed products: treatement with purpose to modify product and/or extend shelf life and/or reduce food safety hazards by preservation techniques and other processing techniques. , D System, treatments to maintain product integrity and or safety , E Systems, treatments to prevent product contamination , F Any other manipulation, treatment, rocessing not being listed in A,B,C,D,E
10	Name9													x	x		x		x		x				x								x	x	x	x	x	x	x	x									x	x				1. Chilled and frozen food , 2. Ambient food , 3. Packaging and packing materials , 4. Consumer products	1 Raw Red Meat , 3 Raw prepared products (meat and vegetarian) , 5 Fruits, vegetables and nuts , 6 Prepared fruits, vegetables and nuts , 7 Dairy, liquid eggs , 8 Cooked meat/fish products , 10 Ready meals and sandwiches, ready to eat desserts , 16 Confectionery			x				1 Raw Red Meat , 5 Fruits, vegetables and nuts , 6 Prepared fruits, vegetables and nuts. , 7 Dairy, liquid eggs	x	01 Red and white meat, poultry and meat products , 05 Fruit and vegetables , 06 Grain products, cereals, industrial bakery and pastry, confectionary, snacks , 07 Combined products , B Pasteurisation with the purpose to reduce food safety hazards (and UHT process) , C Processed products: treatement with purpose to modify product and/or extend shelf life and/or reduce food safety hazards by preservation techniques and other processing techniques. , D System, treatments to maintain product integrity and or safety , E Systems, treatments to prevent product contamination , F Any other manipulation, treatment, rocessing not being listed in A,B,C,D,E
11	Name10		x	x		x		x								x													x	x		x	x	x	x	x	x	x	x	x		x	x	x	x	x
12	Name15														x		x								x								x	x	x	x	x	x	x	x													2 Paper Making , 4 Rigid Plastics , 5 Flexible Plastics , 7 Print Processes
13	Name16								x		x	x		x	x	x	x		x	x	x	x	x		x				x	x	x	x	x	x	x	x	x	x	x	x		x	x	x	x				x	x					1 Raw Red Meat , 2 Raw Poultry , 3 Raw prepared products (meat and vegetarian) , 5 Fruits, vegetables and nuts , 6 Prepared fruits, vegetables and nuts , 7 Dairy, liquid eggs , 8 Cooked meat/fish products , 9 Raw cured and/or fermented meat and fish , 10 Ready meals and sandwiches, ready to eat desserts , 11 Low/high acid in cans/glass , 12 Beverages , 14 Bakery , 15 Dried foods and ingredients , 16 Confectionery , 17 Cereals and snacks , 18 Oils and fats			x						01 Red and white meat, poultry and meat products , 04 Dairy products , 05 Fruit and vegetables , 06 Grain products, cereals, industrial bakery and pastry, confectionary, snacks , 07 Combined products , 08 Beverages , 09 Oils and fats , 10 Dry goods, other ingredients and supllements , A Sterilisation (in final packaging) with the purpose to destroy pathogens , B Pasteurisation with the purpose to reduce food safety hazards (and UHT process) , C Processed products: treatement with purpose to modify product and/or extend shelf life and/or reduce food safety hazards by preservation techniques and other processing techniques. , D System, treatments to maintain product integrity and or safety , E Systems, treatments to prevent product contamination , F Any other manipulation, treatment, rocessing not being listed in A,B,C,D,E
14	Name17	x	x			x		x																									x	x	x	x	x	x	x	x
15	Name18													x					x		x			x									x	x	x	x	x	x	x	x										x					4 Raw fish products and preparations , 5 Fruits, vegetables and nuts , 6 Prepared fruits, vegetables and nuts , 7 Dairy, liquid eggs , 8 Cooked meat/fish products , 9 Raw cured and/or fermented meat and fish , 10 Ready meals and sandwiches, ready to eat desserts , 14 Bakery , 15 Dried foods and ingredients , 16 Confectionery							2 Raw Poultry , 5 Fruits, vegetables and nuts , 6 Prepared fruits, vegetables and nuts. , 7 Dairy, liquid eggs , 10 Ready meals and sandwiches, ready to eat desserts		02 Fish and fish products , 05 Fruit and vegetables , 06 Grain products, cereals, industrial bakery and pastry, confectionary, snacks , 07 Combined products , 10 Dry goods, other ingredients and supllements , B Pasteurisation with the purpose to reduce food safety hazards (and UHT process) , C Processed products: treatement with purpose to modify product and/or extend shelf life and/or reduce food safety hazards by preservation techniques and other processing techniques. , D System, treatments to maintain product integrity and or safety , E Systems, treatments to prevent product contamination , F Any other manipulation, treatment, rocessing not being listed in A,B,C,D,E
16	Name19											x		x	x	x	x				x			x	x		x		x	x	x	x	x	x	x	x	x	x	x	x		x	x	x	x	x						x		1. Chilled and frozen food , 2. Ambient food , 3. Packaging and packing materials , 4. Consumer products	1 Raw Red Meat , 2 Raw Poultry , 3 Raw prepared products (meat and vegetarian) , 4 Raw fish products and preparations , 5 Fruits, vegetables and nuts , 6 Prepared fruits, vegetables and nuts , 8 Cooked meat/fish products , 9 Raw cured and/or fermented meat and fish , 10 Ready meals and sandwiches, ready to eat desserts , 14 Bakery , 16 Confectionery					1.1 Red and white meat, poultry and meat products , 1.2 Fish and fish products , 1.3 Egg and egg products , 1.4 Dairy Products , 1.5 Fruit and vegetables , 1.6 Grain products, cereals, industrial bakery and pastry, confectionary, snacks , 1.7 Combined Products , 1.8 Beverages , 1.9 Oils and fats , 1.10 Dry products, other ingredients and supplements , 3.1 Flexible packaging , 3.2 Rigid plastic , 3.3 Paper , 3.4 Metal , 3.5 Glass , 3.6 Other natural materials	1.1 Red and white meat, poultry and meat products , 1.2 Fish and fish products , 1.5 Fruit and vegetables , 1.6 Grain products, cereals, industrial bakery and pastry, confectionary, snacks , 1.7 Combined products		x	01 Red and white meat, poultry and meat products , 02 Fish and fish products , 05 Fruit and vegetables , 06 Grain products, cereals, industrial bakery and pastry, confectionary, snacks , 07 Combined products , A Sterilisation (in final packaging) with the purpose to destroy pathogens , B Pasteurisation with the purpose to reduce food safety hazards (and UHT process) , C Processed products: treatement with purpose to modify product and/or extend shelf life and/or reduce food safety hazards by preservation techniques and other processing techniques. , D System, treatments to maintain product integrity and or safety , E Systems, treatments to prevent product contamination , F Any other manipulation, treatment, rocessing not being listed in A,B,C,D,E	1.1 Red and white meat, poultry and meat products , 1.2 Fish and fish products , 1.5 Fruit and vegetables , 1.6 Grain products, cereals, industrial bakery and pastry, confectionary, snacks , 1.7 Combined products
Kwalificaties

Sheet: Klant - sector -> this sheet is used to get the sector (column E)

MrExcel - File.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Helper	Name	Status	date	Active	product	standard	MANUEEL	Sector code 2	sector
2	test1IFS v7	test1	customer	21/03/2022	yes	Food	IFS v7	01 Red and white meat, poultry and meat products , 07 Combined products , B Pasteurisation with the purpose to reduce food safety hazards (and UHT p… , D System, treatments to maintain product integrity and or safety , E Systems, treatments to prevent product contamination , F Any other manipulation, treatment, rocessing not being listed in A,B,C,D,E	01	Red and white meat, poultry and meat products
3	test1ACS-018	test1	customer	25/03/2022	yes	Food	ACS-018
4	test1ACS-019 Fenavian	test1	customer	28/03/2022	yes	Food	ACS-019 Fenavian
5	test1BePork Slachthuis	test1	customer	28/03/2022	yes	Food	BePork Slachthuis
6	test1BePork Uitsnijderij	test1	customer	28/03/2022	yes	Food	BePork Uitsnijderij
7	test1Febev + Slachthuis voor varkens	test1	customer	28/03/2022	yes	Food	Febev + Slachthuis voor varkens
8	test1Febev + Uitsnijderij voor varkens	test1	customer	28/03/2022	yes	Food	Febev + Uitsnijderij voor varkens
9	test1BVI/MPT Slachthuis & Uitsnijderij	test1	customer	29/03/2022	yes	Food	BVI/MPT Slachthuis & Uitsnijderij
10	test1Colruyt	test1	customer	29/03/2022	yes	Food	Colruyt
11	test1Export - China	test1	customer	29/03/2022	yes	Food	Export - China
12	test1Export Zuid-Korea Slachthuis	test1	customer	29/03/2022	yes	Food	Export Zuid-Korea Slachthuis
13	test1Export Zuid-Korea Uitsnijderij	test1	customer	30/03/2022	yes	Food	Export Zuid-Korea Uitsnijderij
14	test1Export Zuid-Korea Verwerking	test1	customer	30/03/2022	yes	Food	Export Zuid-Korea Verwerking
Klant - sector

Cell Formulas
Range	Formula
A2:A14	A2	=CONCATENATE([@Name]&[@standard])

So what I need now, is a formula that does the following :
Information (like IFS and name of the auditor come from sheet Planningstabel)

Search for (for example) IFS (in sheet Kwalificaties (= column 64)
Then search for the name of the auditor and compare them to see if he is qualified

Yellow: what it needs to search (sheet planningstabel)

Orange: what the formula needs to do (sheet Kwalificaties)
Look for both and see if it has data in the cell

If there is data => (if possible) compare which data is set in sheet Kwalificaties and what is in sheet Planningstabel

VS

Note: there can be more data in sheet "Kwalificaties" than in sheet "Planningstabel" -> result "Qualified", if visa versa (more in Planningstabel than in "Kwalificaties" => partially qualified
If auditor is not found or the cell in "Kwalificaties" is empty => not qualified

This should be done for every "norm" from sheet Planninstabel
Note: if in sector it says N/A it means there are no specific sectors for this norm -> in sheet "Kwalificaties" it will just say X instead of text

It's a really complex thing to achieve, so if we need to break it down it smaller steps, I'm totally OK with it.


The and result would be this:
AuditorX: Qualified => means the auditor is qualified for ALL norm and can do ALL things
AuditorX : Partially Qualified => means he/she can do some of the norms but not ALL
AdutiorX : Not Qualified => Auditor can not do ANY norm

 

[DarkSmile]

can you also post the potions of the worksheets (Kwalificates, and Klant-Sector) with lookup tables?
Also, please tell what cell formula is the problem, and what you expect the answer to be?

I cannot download your workbook.

Are you able to help me? Thank you !