Need help with formula (Match/Index?)

[DarkSmile]

Hi,

I need some help figuring out a formula, I'm trying to achieve the following:

I have a list with items and client names

Book3
	A	B	C	D	E
1	Normen	Column Labels
2		Business a	Business b	Business c	Business d
3	AOECS Module for Gluten Free Foods
4	ASDA-module
5	BRC Agents & Brokers
6	BRC Packaging
7	BRC S&D
8	BRC v8
9	FSMA Module Preventive Controls Preparedness
10	FSSC 22000 v5
11	GFCP
12	GLOBAL GAP Chain of Custody
13	Head Office
14	IFS Broker
15	IFS Cash & Carry v2
16	IFS L
17	IFS Food
18	IFS v7
19	IFS Wholesale v2
20	Meat Supply Chain
21	MSC
22	Whole Sale-module
Sheet1

I have another sheet with a list of all clients etc

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Organisatie	Audit: id	Regeling	Uitvoering	Referentiedatum	Datum uitgifte	Geldig van	Geldig tot	Medewerker	Status	Datum_zwart	Medewerker_zwart	test
2	Business a	1111	IFS Food	04/03/2021	04/03/2021	23/04/2021	23/04/2021	25/04/2022	Bob x	200-1 SendDocumentsCompleted	05/02/2021 09:57	Person x	x
3	Business b	2222	IFS Food	02/03/2022	02/03/2022		28/04/2022	25/04/2023	Bob y	200 Rapportage definitief positief	31/01/2022 13:46	Person y	x
4	Business c	3333	IFS Logistics	18/10/2021	18/10/2021		17/12/2021	12/12/2022	Bob z	200 Rapportage definitief positief	07/09/2021 12:17	Person z	x
5	Business d	4444	IFS Logistics	22/09/2022	08/08/2022				Bob a	104 Uitvoeren audit	23/08/2022 16:11	Person x	x
6	Business e	5555	IFS Global Markets Food	28/06/2022	08/06/2022		15/07/2022	01/09/2023	Bob b	200 Rapportage definitief positief	24/06/2022 14:07	Person y	x
7	Business f	6666	IFS Food	21/09/2021	22/09/2021		17/11/2021	26/11/2022	Bob c	200 Rapportage definitief positief	27/08/2021 13:03	Person z	x
8	Business g	7777	IFS Food	12/09/2022	22/06/2022				Bob d	100-1 Bevestiging ontvangen	02/07/2022 10:50	Person x	x
9	Business h	8888	IFS Food	07/09/2021	07/09/2021		17/11/2021	22/11/2022	Bob e	200 Rapportage definitief positief	10/09/2021 15:48	Person y	x
10	Business i	9999	IFS Food	07/09/2021	11/08/2021		27/10/2021	21/12/2022	Bob f	200 Rapportage definitief positief	16/07/2021 12:45	Person z	x
11	Business j	10000	IFS Food	16/08/2022	05/07/2022				Bob g	104 Uitvoeren audit	01/08/2022 08:28	Person x	x
12	Business k	11111	IFS Food	04/10/2021	02/08/2021		04/10/2021	12/12/2022	Bob H	200 Rapportage definitief positief	16/07/2021 11:54	Person y	x
13	Business l	12222	IFS Food	13/09/2022	28/06/2022				Bob i	104 Uitvoeren audit	04/07/2022 09:22	Person z	x
14	Business m	13333	IFS Food	02/08/2021	02/08/2021		19/10/2021	15/10/2022	Bob j	200 Rapportage definitief positief	16/07/2021 14:02	Person x	x
15	Business n	14444	BRC Food	05/08/2021	05/08/2021	27/10/2021	27/10/2021	30/09/2022	Bob k	200-1 SendDocumentsCompleted	29/06/2021 11:44	Person y	x
Certivin

What I'm trying to achieve is a lookup:

Lookup business a and return if it has norm x

For example: Business a has IFS Food, in sheet1 i would like to see a x in IFS food, and this for all the items it can find (1 client can have multiple items)

Book3
	A	B	C	D	E
1	Normen	Column Labels
2		Business a	Business b	Business c	Business d
3	AOECS Module for Gluten Free Foods
4	ASDA-module
5	BRC Agents & Brokers
6	BRC Packaging
7	BRC S&D
8	BRC v8
9	FSMA Module Preventive Controls Preparedness
10	FSSC 22000 v5
11	GFCP
12	GLOBAL GAP Chain of Custody
13	Head Office
14	IFS Broker
15	IFS Cash & Carry v2
16	IFS Logistics			x
17	IFS Food	x	x
18	IFS v7
19	IFS Wholesale v2
20	Meat Supply Chain
21	MSC
22	Whole Sale-module
Sheet1

I tried some indexes, but unable to achieve what I want:

First one, does find the right item and returns it = (not what i'm trying to achieve

=INDEX(Certivin!$C$2:$C$15,MATCH(Sheet1!B2,Certivin!$A$2:$A$15,0))

Second one, doesn't work, I thought it would.. =

=INDEX(Certivin!M2:M15,MATCH(1,Sheet1!B2=Certivin!A2:A15)*(Sheet1!A17=Certivin!C2:C15),0)

Book3
	A	B	C	D	E
1	Normen	Column Labels
2		Business a	Business b	Business c	Business d
3	AOECS Module for Gluten Free Foods	IFS Food
4	ASDA-module
5	BRC Agents & Brokers
6	BRC Packaging
7	BRC S&D
8	BRC v8
9	FSMA Module Preventive Controls Preparedness
10	FSSC 22000 v5
11	GFCP
12	GLOBAL GAP Chain of Custody
13	Head Office
14	IFS Broker
15	IFS Cash & Carry v2
16	IFS Logistics
17	IFS Food	#N/A
18	IFS v7	#N/A
19	IFS Wholesale v2	#N/A
20	Meat Supply Chain	#N/A
21	MSC	#N/A
22	Whole Sale-module	#N/A
Sheet1

Cell Formulas
Range	Formula
B3	B3	=INDEX(Certivin!$C$2:$C$15,MATCH(Sheet1!B2,Certivin!$A$2:$A$15,0))
B17:B30	B17	=INDEX(Certivin!M2:M15,MATCH(1,Sheet1!B2=Certivin!A2:A15)*(Sheet1!A17=Certivin!C2:C15),0)
Dynamic array formulas.

Sorry if it's not clear, its really hard to explain...

Thank you
David

 

[Fluff]

In B3 try

=IFNA(INDEX(Certivin!$M$2:$M$15,MATCH(1,(B$2=Certivin!$A$2:$A$15)*($A3=Certivin!$C$2:$C$15),0)),"")

dragged down & across

 

[DarkSmile]

In B3 try

=IFNA(INDEX(Certivin!$M$2:$M$15,MATCH(1,(B$2=Certivin!$A$2:$A$15)*($A3=Certivin!$C$2:$C$15),0)),"")

dragged down & across

Just wonderfull, thank you so mutch again !

 

[Fluff]

Glad to help & thanks for the feedback.