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]

This one is Office365 Version 2108 (Build 14326.20852)

 

[Georgiboy]

Is it the same with the file in this link?

 

[Fluff]

Could be wrong, but don't think that release includes LET.
@DarkSmile if you type =le into a cell, is LET one of the options?

 

[DarkSmile]

Is it the same with the file in this link?

In your file it works, if tried some things, like deleting table in Klant - sector like you did, but doesn't seem to help, here is my test file

 

[DarkSmile]

Could be wrong, but don't think that release includes LET.
@DarkSmile if you type =le into a cell, is LET one of the options?

Yes, here are my option for =LE

 

[Fluff]

Ok, I was wrong. You obviously do have LET.

 

[Georgiboy]

In your file it works, if tried some things, like deleting table in Klant - sector like you did, but doesn't seem to help, here is my test file

I can't access the file

 

[DarkSmile]

I can't access the file

What about now? FILE

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Need help finding right formula : Index/Match ; Vlookup?
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[DarkSmile]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Need help finding right formula : Index/Match ; Vlookup?
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

I'm very sorry, not to talk myself out of it, but I did to get faster response, it was for my job and had a deadline.
For anyone interested, this is a working formula :

=LET( A,Kwalificaties!$A$2:$A$16, B,Kwalificaties!$B$2:$BV$16, C,FILTER(B,A=Planningstabel!G2), D,Kwalificaties!$B$1:$BV$1, E,FILTER(C,ISNUMBER(MATCH(D,$D$2#,0))), F,IFERROR(COLUMNS(FILTER(E,E="x")),0), IF(F=0,"No Qualifications",IF(F=ROWS($D$2#),"All","Partial")))