Need help with formula (Match/Index?)

DarkSmile

Board Regular
Joined
Feb 22, 2021
Messages
90
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1NormenColumn Labels
2Business aBusiness bBusiness cBusiness d
3AOECS Module for Gluten Free Foods
4ASDA-module
5BRC Agents & Brokers
6BRC Packaging
7BRC S&D
8BRC v8
9FSMA Module Preventive Controls Preparedness
10FSSC 22000 v5
11GFCP
12GLOBAL GAP Chain of Custody
13Head Office
14IFS Broker
15IFS Cash & Carry v2
16IFS L
17IFS Food
18IFS v7
19IFS Wholesale v2
20Meat Supply Chain
21MSC
22Whole Sale-module
Sheet1


I have another sheet with a list of all clients etc

Book3
ABCDEFGHIJKLM
1OrganisatieAudit: idRegelingUitvoeringReferentiedatumDatum uitgifteGeldig vanGeldig totMedewerkerStatusDatum_zwartMedewerker_zwarttest
2Business a1111IFS Food04/03/202104/03/202123/04/202123/04/202125/04/2022Bob x200-1 SendDocumentsCompleted05/02/2021 09:57Person xx
3Business b2222IFS Food02/03/202202/03/202228/04/202225/04/2023Bob y200 Rapportage definitief positief31/01/2022 13:46Person yx
4Business c3333IFS Logistics18/10/202118/10/202117/12/202112/12/2022Bob z200 Rapportage definitief positief07/09/2021 12:17Person zx
5Business d4444IFS Logistics22/09/202208/08/2022Bob a104 Uitvoeren audit23/08/2022 16:11Person xx
6Business e5555IFS Global Markets Food28/06/202208/06/202215/07/202201/09/2023Bob b200 Rapportage definitief positief24/06/2022 14:07Person yx
7Business f6666IFS Food21/09/202122/09/202117/11/202126/11/2022Bob c200 Rapportage definitief positief27/08/2021 13:03Person zx
8Business g7777IFS Food12/09/202222/06/2022Bob d100-1 Bevestiging ontvangen02/07/2022 10:50Person xx
9Business h8888IFS Food07/09/202107/09/202117/11/202122/11/2022Bob e200 Rapportage definitief positief10/09/2021 15:48Person yx
10Business i9999IFS Food07/09/202111/08/202127/10/202121/12/2022Bob f200 Rapportage definitief positief16/07/2021 12:45Person zx
11Business j10000IFS Food16/08/202205/07/2022Bob g104 Uitvoeren audit01/08/2022 08:28Person xx
12Business k11111IFS Food04/10/202102/08/202104/10/202112/12/2022Bob H200 Rapportage definitief positief16/07/2021 11:54Person yx
13Business l12222IFS Food13/09/202228/06/2022Bob i104 Uitvoeren audit04/07/2022 09:22Person zx
14Business m13333IFS Food02/08/202102/08/202119/10/202115/10/2022Bob j200 Rapportage definitief positief16/07/2021 14:02Person xx
15Business n14444BRC Food05/08/202105/08/202127/10/202127/10/202130/09/2022Bob k200-1 SendDocumentsCompleted29/06/2021 11:44Person yx
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
ABCDE
1NormenColumn Labels
2Business aBusiness bBusiness cBusiness d
3AOECS Module for Gluten Free Foods
4ASDA-module
5BRC Agents & Brokers
6BRC Packaging
7BRC S&D
8BRC v8
9FSMA Module Preventive Controls Preparedness
10FSSC 22000 v5
11GFCP
12GLOBAL GAP Chain of Custody
13Head Office
14IFS Broker
15IFS Cash & Carry v2
16IFS Logisticsx
17IFS Foodxx
18IFS v7
19IFS Wholesale v2
20Meat Supply Chain
21MSC
22Whole 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
Code:
=INDEX(Certivin!$C$2:$C$15,MATCH(Sheet1!B2,Certivin!$A$2:$A$15,0))

Second one, doesn't work, I thought it would.. =
Code:
=INDEX(Certivin!M2:M15,MATCH(1,Sheet1!B2=Certivin!A2:A15)*(Sheet1!A17=Certivin!C2:C15),0)

Book3
ABCDE
1NormenColumn Labels
2Business aBusiness bBusiness cBusiness d
3AOECS Module for Gluten Free FoodsIFS Food
4ASDA-module
5BRC Agents & Brokers
6BRC Packaging
7BRC S&D
8BRC v8
9FSMA Module Preventive Controls Preparedness
10FSSC 22000 v5
11GFCP
12GLOBAL GAP Chain of Custody
13Head Office
14IFS Broker
15IFS Cash & Carry v2
16IFS Logistics
17IFS Food#N/A
18IFS v7#N/A
19IFS Wholesale v2#N/A
20Meat Supply Chain#N/A
21MSC#N/A
22Whole Sale-module#N/A
Sheet1
Cell Formulas
RangeFormula
B3B3=INDEX(Certivin!$C$2:$C$15,MATCH(Sheet1!B2,Certivin!$A$2:$A$15,0))
B17:B30B17=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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
In B3 try
Excel Formula:
=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
 
Upvote 0
Solution
In B3 try
Excel Formula:
=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 !
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,224,812
Messages
6,181,089
Members
453,021
Latest member
Justyna P

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
Back
Top