Lookup/Vlookup/Hlookup/Index & Match? Need help

[DarkSmile]

Hi,

I need some help finding the right formula. I'm trying the following :

Look if "Name 1" has an x in the right "norm"

For example, look if name 1 has an X in IFS , IFS is located in colum 64 (if it can help) , if it has X return yes or anything (don't care)
Preferable it's a nested formula, so look if Name 1 has an X in all the norms (located in D)

MrExcel - File.xlsx
	A	B	C	D	E	F	G
1	Klantnaam	Klantnaam2	Normen	Normen2	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	64		Name1
3		test1		ACS-018	16		Name2
4		test1		ACS-019 Fenavian	17		Name3
5		test1		BePork Slachthuis	29		Name4
6		test1		BePork Uitsnijderij	30		Name5
7		test1		Febev + Slachthuis voor varkens	43		Name6
8		test1		Febev + Uitsnijderij voor varkens	45		Name7
9		test1		BVI/MPT Slachthuis & Uitsnijderij	33		Name8
10		test1		Colruyt	35		Name9
11		test1		Export - China	36		Name10
12		test1		Export Zuid-Korea Slachthuis	38		Name11
13		test1		Export Zuid-Korea Uitsnijderij	39		Name12
14		test1		Export Zuid-Korea Verwerking	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)
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
E2:E25	E2	=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

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					x							x		x
3	Name2								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
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			x			x				x		x
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				x	x		x					x		x
8	Name7	x	x											x	x		x							x	x								x	x	x	x	x	x	x	x						x									x		x			x		x	x	x
9	Name8								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
10	Name9													x	x		x		x		x				x								x	x	x	x	x	x	x	x									x	x				x	x			x				x	x	x
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													x
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					x			x						x
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					x							x		x
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		x	x					x	x		x	x	x
Kwalificaties

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])

Sorry for bad explanation

Thank you
David

 

[DarkSmile]

anyone pls?

 

[Georgiboy]

Not sure i fully understand but try the below as a start, thought it might be a good use of index/ match/ match

=IF(INDEX(Kwalificaties!$A$1:$BV$16,MATCH(G2,Kwalificaties!$A$1:$A$16,0),MATCH(D2,Kwalificaties!$A$1:$BV$1,0))="x","Yes","No")

 

[DarkSmile]

Not sure i fully understand but try the below as a start, thought it might be a good use of index/ match/ match

=IF(INDEX(Kwalificaties!$A$1:$BV$16,MATCH(G2,Kwalificaties!$A$1:$A$16,0),MATCH(D2,Kwalificaties!$A$1:$BV$1,0))="x","Yes","No")

Hi, Yes this is what I need, I just need to adjust in now so it checks all "normen" :

I tried this, but not working "to many arguments"

=IF(INDEX(Kwalificaties!$A$1:$BV$16,MATCH(G2,Kwalificaties!$A$1:$A$16,0),MATCH(D2,Kwalificaties!$A$1:$BV$1,0))="x",INDEX(Kwalificaties!$A$1:$BV$16,MATCH(G2,Kwalificaties!$A$1:$A$16,0),MATCH(D3,Kwalificaties!$A$1:$BV$1,0))="x",INDEX(Kwalificaties!$A$1:$BV$16,MATCH(G2,Kwalificaties!$A$1:$A$16,0),MATCH(D4,Kwalificaties!$A$1:$BV$1,0))="x","Yes","No")

 

[Georgiboy]

Will it not find lots of X's that way?

What happens if there was an X in all of them? None of them? Some of them?

 

[DarkSmile]

Will it not find lots of X's that way?

What happens if there was an X in all of them? None of them? Some of them?

So yeah, it needs to check for multiple criteria and then return a yes or no.
So it will find multiple X's but in different columns:

Example;
Check if name1 has an x in all of those "normen" and return a yes or no

So for example, I will just check the first 4, (but in reallity it needs to check all)

Name1 has no X in ACS-018 and BePork Slachthuis, the formula should return a NO ; (OR if possible it needs to return "partially").

 

[Georgiboy]

I changed all of your X's in sheet 'Kwalificaties' to 1's to make things easier. I then created the formula below (I am not the best with the new functions) to calculate the result. I added the IFERROR at the front as some of the names don't exist in 'Kwalificaties':

15.02.xlsm
	A	B	C	D	E	F	G
1	Klantnaam	Klantnaam2	Normen	Normen2	Column	Gekwalificeerd?	Auditor
2	test1	test1				None	Name1
3		test1				None	Name2
4		test1				Partial	Name3
5		test1				Partial	Name4
6		test1				All	Name5
7		test1				All	Name6
8		test1				None	Name7
9		test1				Partial	Name8
10		test1				Partial	Name9
11		test1				Partial	Name10
12		test1				None	Name11
13		test1				None	Name12
14		test1				None	Name13
15		test1				None	Name14
16		test1				Partial	Name15
17		test1				All	Name16
18		test1				Partial	Name17
19		test1				Partial	Name18
20		test1				All	Name19
21		test1				None	Name20
22		test1				None	Reserve
23		test1				None	Reserve
24		test1				None	Reserve
25		test1				None	Reserve
Planningstabel

Cell Formulas
Range	Formula
F2:F25	F2	=IFERROR(LET(x,FILTER('Klant - sector'!$G$1:$G$14,'Klant - sector'!$B$1:$B$14=Planningstabel!$A$2),r,SUM(INDEX(Kwalificaties!$A$1:$BV$16,MATCH(G2,Kwalificaties!$A$1:$A$16,0),MATCH(x,Kwalificaties!$A$1:$BV$1,0))),IF(r=COUNTA(x),"All",IF(r>0,"Partial","None"))),"None")

Keep in mind that i changed some of the data in 'Kwalificaties' so the results above will not be as your original data would have created.

 

[Georgiboy]

You could also update the last "None" in the formula to be "Not Found" as it is there when the listed name is not found on the 'Kwalificaties' sheet.

 

[DarkSmile]

So I changed all the X's in sheet Kwalificaties to a 1, like you said right?
But i'm not getting the same results as you ?

Test - File.xlsx
	A	B	C	D	E	F	G
1	Klantnaam	Klantnaam2	Normen	Normen2	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			None	Name1
3		test1				None	Name2
4		test1				None	Name3
5		test1				None	Name4
6		test1				None	Name5
7		test1				None	Name6
8		test1				None	Name7
9		test1				None	Name8
10		test1				None	Name9
11		test1				None	Name10
12		test1				None	Name11
13		test1				None	Name12
14		test1				None	Name13
15		test1				None	Name14
16		test1				None	Name15
17		test1				None	Name16
18		test1				None	Name17
19		test1				None	Name18
20		test1				None	Name19
21		test1				None	Name20
22		test1				None	Reserve
23		test1				None	Reserve
24		test1				None	Reserve
25		test1				None	Reserve
Planningstabel

Cell Formulas
Range	Formula
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(LET(x,FILTER('Klant - sector'!$G$1:$G$14,'Klant - sector'!$B$1:$B$14=Planningstabel!$A$2),r,SUM(INDEX(Kwalificaties!$A$1:$BV$16,MATCH(G2,Kwalificaties!$A$1:$A$16,0),MATCH(x,Kwalificaties!$A$1:$BV$1,0))),IF(r=COUNTA(x),"All",IF(r>0,"Partial","None"))),"None")

Named Ranges
Name	Refers To	Cells
Kwalificaties!_FilterDatabase	=Kwalificaties!$A$1:$BV$16	F2:F25
'Klant - sector'!ExternalData_1	='Klant - sector'!$B$1:$J$14	F2:F25

Test - 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	E1port - China	E1port Zuid-Korea Koelhuis	E1port Zuid-Korea Slachthuis	E1port Zuid-Korea Uitsnijderij	E1port 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								1					1	1		1			1					1								1	1	1	1	1	1	1	1										1					1							1		1
3	Name2								1	1	1			1	1		1		1		1		1		1								1	1	1	1	1	1	1	1									1	1			1		1		1	1				1		1
4	Name3															1													1	1		1	1	1	1	1	1	1	1	1		1	1	1	1
5	Name4																																1	1	1	1	1	1	1	1
6	Name5									1		1			1	1	1								1		1	1	1	1	1	1	1	1	1	1	1	1	1	1		1	1	1	1	1			1			1			1			1				1		1
7	Name6								1			1		1	1	1	1		1	1	1				1		1		1	1	1	1	1	1	1	1	1	1	1	1		1	1	1	1					1				1	1		1					1		1
8	Name7	1	1											1	1		1							1	1								1	1	1	1	1	1	1	1						1									1		1			1		1	1	1
9	Name8								1		1		1	1	1		1	1	1		1												1	1	1	1	1	1	1	1										1			1	1	1		1			1		1	1	1
10	Name9													1	1		1		1		1				1								1	1	1	1	1	1	1	1									1	1				1	1			1				1	1	1
11	Name10		1	1		1		1								1													1	1		1	1	1	1	1	1	1	1	1		1	1	1	1	1
12	Name15														1		1								1								1	1	1	1	1	1	1	1													1
13	Name16								1		1	1		1	1	1	1		1	1	1	1	1		1				1	1	1	1	1	1	1	1	1	1	1	1		1	1	1	1				1	1					1			1						1
14	Name17	1	1			1		1																									1	1	1	1	1	1	1	1
15	Name18													1					1		1			1									1	1	1	1	1	1	1	1										1					1							1		1
16	Name19											1		1	1	1	1				1			1	1		1		1	1	1	1	1	1	1	1	1	1	1	1		1	1	1	1	1						1		1	1					1	1		1	1	1
Kwalificaties

 

[Georgiboy]

I see you have excel versions 2016 & 365 listed, which are you using to view this?