Formula to Find IF and Match to Setting Map Output In Column H

[Lukma]

Hi Good friends
Its Been Long Please i need a help with formula in Column H2 to give me the out result in Column with i have created , here is what i need formula to do I have in column A2:A100 List Now i Have List of Activity in column B2:100 and i Have in Stand by reason List in Column D2:D100 and i Have In Location List in Column E2:E100 and i have destination list in Column G2:G100 now i Created a Other List from Column J and K and L , M the Output result Map for each Activity In Column N and i also Create and Copy Location and Destination List to P and Q Now formula i need is IF Column A2= and Matches List In Column J 2:J100 and column B=Matches Column K2:K100 and Column D=Matches any In Column M2:M100 and Column E=Matches Column P2:P100 and column G= Matches Column Q2:Q100 then Result Output In Column H2 will be " Waiting Berth Offshore" i have placed Example in my Output in Column H2 appreciate any formula that can get me the result output

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Productive Status	Activity	Stand By Type	Stand By Reason	Location	Location Category	Destination	Output Result		Productive Status	Activtiy	Stand By Type	Stand By Reason	OutPut		Location	Destination
2	Idle	STAND BY AT ANCHORAGE	At anchorage	Berth not available	Zakum East anchorage	Mooring Buoy	Zakum East anchorage	Waiting Berth Offshore		Operation	LIQUID BULK OPS	N/A	N/A	Working MOSB		Zakum East anchorage	Zakum East anchorage
3	Operation	MANOEUVRING/ SHIFTING	N/A	N/A	Musaffah Short Stay Anchorage	Mooring Buoy	New Musaffah Channel			Operation	DECK CARGO OPS	N/A	N/A	Working MOSB		Musaffah Short Stay Anchorage	New Musaffah Channel
4	Transit	TRANSIT TO PORT	N/A	N/A	New Musaffah Channel	Port	ADNOC Port			Operation	DRY BULK OPS	N/A	N/A	Working MOSB		New Musaffah Channel	ADNOC Port
5	Operation	MANOEUVRING/ SHIFTING	N/A	N/A	ADNOC Port	Port	ADNOC Port			Operation	LIQUID BULK OPS	N/A	N/A	Working Offshore		ADNOC Port	ADNOC Port
6	Idle	STAND BY ALONGSIDE	Alongside	Berth not available	ADNOC Port	Port	ADNOC Port			Operation	DECK CARGO OPS	N/A	N/A	Working Offshore		ADNOC Port	ADNOC Port
7	Operation	MANOEUVRING/ SHIFTING	N/A	N/A	ADNOC Port	Port	ADNOC Port			Operation	DRY BULK OPS	N/A	N/A	Working Offshore		ADNOC Port	ADNOC Port
8	Idle	STAND BY ALONGSIDE	Alongside	No work order from Onshore site	ADNOC Port	Port	ADNOC Port			Operation	LIQUID BULK OPS	N/A	N/A	Working MUG		ADNOC Port	ADNOC Port
9	Operation	LIQUID BULK OPS	N/A	N/A	ADNOC Port	Port	ADNOC Port			Operation	DECK CARGO OPS	N/A	N/A	Working MUG		ADNOC Port	ADNOC Port
10	Idle	STAND BY ALONGSIDE	Alongside	No work order from Onshore site	ADNOC Port	Port	ADNOC Port			Operation	DRY BULK OPS	N/A	N/A	Working MUG		ADNOC Port	ADNOC Port
11	Operation	DECK CARGO OPS	N/A	N/A	ADNOC Port	Port	ADNOC Port			Operation	LIQUID BULK OPS	N/A	N/A	Working RUWAIS		ADNOC Port	ADNOC Port
12	Idle	STAND BY ALONGSIDE	Alongside	No work order from Onshore site	ADNOC Port	Port	ADNOC Port			Operation	DECK CARGO OPS	N/A	N/A	Working RUWAIS		ADNOC Port	ADNOC Port
13	Operation	DECK CARGO OPS	N/A	N/A	ADNOC Port	Port	ADNOC Port			Operation	DRY BULK OPS	N/A	N/A	Working RUWAIS		ADNOC Port	ADNOC Port
14	Idle	STAND BY ALONGSIDE	Alongside	No work order from Onshore site	ADNOC Port	Port	ADNOC Port			Transit	TRANSIT TO FIELD	N/A	N/A	Depart MOSB to Site		ADNOC Port	ADNOC Port
15	Operation	DECK CARGO OPS	N/A	N/A	ADNOC Port	Port	ADNOC Port			Transit	TRANSIT TO FIELD	N/A	N/A	Depart Site to MOSB		ADNOC Port	ADNOC Port
16	Idle	STAND BY ALONGSIDE	Alongside	Waiting for linemen	ADNOC Port	Port	ADNOC Port			Transit	TRANSIT TO FIELD	N/A	N/A	Depart MUG to Site		ADNOC Port	ADNOC Port
17	Operation	DECK CARGO OPS	N/A	N/A	ADNOC Port	Port	ADNOC Port			Transit	TRANSIT TO FIELD	N/A	N/A	Depart Site to MUG		ADNOC Port	ADNOC Port
18	Idle	STAND BY ALONGSIDE	Alongside	Waiting for port permission	ADNOC Port	Port	ADNOC Port			Transit	TRANSIT TO FIELD	N/A	N/A	Depart MOSB to Site		ADNOC Port	ADNOC Port
19	Operation	MANOEUVRING/ SHIFTING	N/A	N/A	ADNOC Port	Port	New Musaffah Channel			Transit	TRANSIT TO FIELD	N/A	N/A	Depart Site to MOSB		ADNOC Port	New Musaffah Channel
20										Transit	TRANSIT TO FIELD	N/A	N/A	Depart MUG to Site		New Musaffah Channel	Musaffah Short Stay Anchorage
21										Transit	TRANSIT TO FIELD	N/A	N/A	Depart Site to MUG		BU SIKEEN ISLAND	BU SIKEEN ISLAND
22										Transit	TRANSIT TO PORT	N/A	N/A	Op		BU SIKEEN ISLAND	AL QATIA ISLAND
23										Transit	TRANSIT TO PORT	N/A	N/A	Op		AL QATIA ISLAND	AL QATIA ISLAND
24										Idle	STAND BY AT ANCHORAGE	At anchorage	Berth not available	Waiting Berth Offshore		RUWAIS	RUWAIS
Sheet4

 

[Sam_D_Ben]

Something like this,

=IF(AND(COUNTIFS(J$2:J$100, A19) > 0, COUNTIFS(K$2:K$100, B19) > 0, COUNTIFS(M$2:M$100, D19) > 0, COUNTIFS(P$2:P$100, E19) > 0, COUNTIFS(Q$2:Q$100, G19) > 0), "Waiting Berth Offshore", "")

 

[Lukma]

Dear Sam D
Thanks let me give it a try appreciate, formula didnt not right with i want however please look at the formula is there any better way without slowing my excel this is something am ooking to get

=IF(AND(ISNUMBER(MATCH(A2, J$2:J$100, 0)),ISNUMBER(MATCH(B2, K$2:K$100, 0)),ISNUMBER(MATCH(D2, M$2:M$100, 0)),ISNUMBER(MATCH(E2, P$2:P$100)),ISNUMBER(MATCH(G2, Q$2:Q$100))),INDEX(N$2:N$100, MATCH(1, (J$2:J$100=A2) * (K$2:K$100=B2) * (M$2:M$100=D2), 0)),
    "No Match")