Show the value on the column depending on the given value from another column

[mark692]

I want a formula that i will put on remarks column, I want it to show the name of the leader from column "Leader" depending on what team the name belong. I have a sample table below. On my example team orange has a leader James thats why on the column remarks James appear depending on the coresponding team name from column "Team". thanks

Book1
	A	B	C
1	Team	Leader	Remarks
2	Orange	James	James
3	orange		James
4	orange		James
5	Orange		James
6	Apple	Mimi	Mimi
7	Apple		Mimi
8	Apple		Mimi
9	Grapes	Larry	Larry
10	Grapes		Larry
11	Grapes		Larry
12	Grapes		Larry
Sheet1

 

[mark692]

You are welcome.

i tried it on my actual table this error appear sir "sort method or Range class failed"

 

[Joe4]

i tried it on my actual table this error appear sir "sort method or Range class failed"

That is the danger when you post an example that may not accurately reflect you actual file - the solution is geared toward the example you posted (as we have nothing else to go on).
I often advise people not to oversimplify the problem for our sake. Otherwise, you may get a solution that works for the question you asked, but not for the real problem that you have.

I would recommend posting a sample of the real file here, and we will see if we can adapt the solution to work for your actual data.

 

[mark692]

do i have to upload my file? or this will do?

payables.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2
3
4	Date	For Invoicing of DR	DR	SI	name	Type Of payment	Credit memo	Type	Reference No.	Check Bank	check no.	check date	amount	Voucher No.	Group Code	Posted On	Voucher No. Group	Debited To bank
5	6/27/20			66935	RYC NC	Check				Metrobank	4899289492	9/29/2020	18,235.71	1 SI	1	10/29/20 16:03
6					RYC NC	2307							164.29		1	10/29/20 16:03
7	7/10/20				Syspr Tr & Srvc Cntr	Check				Metrobank	4899297991	11/17/2020	38,338.97	1 DR	2	10/30/20 13:40
8	7/11/20		5006		Syspr Tr & Srvc Cntr	Check				Metrobank	4899297992	11/18/2020	38,338.97		2	10/30/20 13:40
9	7/16/20		5188-sys		Syspr Tr & Srvc Cntr	Check				Metrobank	4899297994	11/19/2020	38,338.97		2	10/30/20 13:40
10	7/10/20		4974		Syspr Tr & Srvc Cntr	Credit Memo	1	2307				11/7/2020	3,635.30		2	10/30/20 13:03
11	7/10/20				Syspr Tr & Srvc Cntr	Credit Memo	2	2307				11/7/2020	1,349.78		2	10/30/20 13:11
12	7/22/20		5440		Syspr Tr & Srvc Cntr										2	10/30/20 13:40
13	7/23/20		5507		Syspr Tr & Srvc Cntr										2	10/30/20 13:40
14	7/27/20		5662		Syspr Tr & Srvc Cntr										3	10/30/20 13:40
15	7/24/20		5558		Syspr Tr & Srvc Cntr	Check				Metrobank	4899297994	11/24/2020	22,086.50	2 DR	3	10/30/20 13:40
16	7/27/20		5652		Syspr Tr & Srvc Cntr	Check				Metrobank	4899297995	11/25/2020	22,086.50		3	10/30/20 13:40
17	7/29/20		5778		Syspr Tr & Srvc Cntr										3	10/30/20 13:40
18	7/23/20			2889	P Blu Whl Crprtn										4	10/30/20 13:56
19	7/27/20			2916	P Blu Whl Crprtn										4	10/30/20 13:56
20	7/9/20			2757	P Blu Whl Crprtn	Check				Metrobank	4899297996	10/20/2020	23,454.08	2 SI	4	10/30/20 13:56
21					P Blu Whl Crprtn	2307						10/20/2020	211.30		4	10/30/20 13:56
22	7/25/20			3667	Mrn Lzs Tr Cntr	Cash							2,279.46	3 SI	5	10/30/20 17:00
23					Mrn Lzs Tr Cntr	2307						7/25/2020	20.54		5	10/30/20 17:00
24	7/23/20			67247	RYC NC	Check				Metrobank	4899297997	10/22/2020	27,561.69	4 SI	6	10/30/20 17:10
25					RYC NC	2307						7/24/2020	248.30		6	10/30/20 17:10
26	8/25/20			67558	RYC NC										7	10/30/20 17:10
27	8/11/20			67422	RYC NC	Check				Metrobank	4899297998	11/17/2020	33,379.28	5 SI	7	10/30/20 17:10
28	8/18/20			67533	RYC NC	Check				Metrobank	4899297999	11/18/2020	33,379.28		7	10/30/20 17:10
29					RYC NC	2307						8/11/2020	601.43		7	10/30/20 17:10
30	7/25/20			10632	Tywst Mrktng	Credit Memo	3	2307				11/25/2020	3,125.00		8	10/31/20 10:06
31	8/25/20			10557	cml Cmpny, nc	Cash							5,864.16	6 SI	9	10/31/20 12:48
32					cml Cmpny, nc	2307						8/25/2020	52.83		9	10/31/20 12:48
33				0	cml Cmpny, nc	2307						8/27/2020	285.80		10	10/31/20 12:54
34	8/27/20			10565	cml Cmpny, nc	Check				Metrobank	4899279955	8/27/2020	31,724.20	7 SI	10	10/31/20 12:54
35	8/26/20			20704	CT CRTCH NC	Check				Metrobank	4899297919	12/24/2020	33,680.57	8 SI	11	10/31/20 13:43
36					CT CRTCH NC	2307						12/24/2020	303.43		11	10/31/20 13:43
37	8/14/20			107466	ng h Tub Crprtn										12	10/31/20 13:43
38	8/4/20			107330	ng h Tub Crprtn	Check				Metrobank	4899297911	11/12/2020	25,979.94	9 SI	12	10/31/20 13:43
39					ng h Tub Crprtn	2307						8/11/2020	234.05		12	10/31/20 13:43
40	8/11/20			1.01E+08	Sun Mstr Sls Crp										13	10/31/20 16:36
41	8/24/20			1.01E+08	Sun Mstr Sls Crp										13	10/31/20 16:36
42	8/5/20			1.01E+08	Sun Mstr Sls Crp	Check				Metrobank	4899297912	10/15/2020	32,526.96	10 SI	13	10/31/20 16:36
43					Sun Mstr Sls Crp	2307						8/7/2020	293.04		13	10/31/20 16:36
44	11/2/20		1120200003		CMR8 Tugh Tyr Crprtn	Check				Metrobank	4899289499	11/2/2020	25,336.00	3 DR	14	11/2/20 10:54
45	8/28/20			32040	Mult-Brn Qulty Trng Crp	Credit Memo	4	2307				12/28/2020	5,178.57		14	10/31/20 16:49
46	11/2/20		9897		Syspr Tr & Srvc Cntr	Check				Metrobank	4899297914	11/2/2020	22,440.00	4 DR	15	11/2/20 14:16
47	8/4/20		25923		Syspr Tr & Srvc Cntr	Credit Memo	5	2307	1-Jan-00		15278	12/2/2020	3,229.91		16	11/3/20 15:29
48	8/5/20		0	540	LGC Tr Supply	Cash							40,832.14	11 SI	17	11/3/20 15:44
49					LGC Tr Supply	2307						8/5/2020	367.86		17	11/3/20 15:44
50	7/2/20		9181		strnc Mult Tr Crprtn										18	11/4/20 15:54
51	7/7/20		9294		strnc Mult Tr Crprtn										18	11/4/20 15:54
52	7/1/20		9065		strnc Mult Tr Crprtn	Check				Metrobank	4899297915	11/5/2020	203,921.00	5 DR	18	11/4/20 15:54
53	7/8/20		9418		strnc Mult Tr Crprtn										18	11/4/20 15:54
54	7/9/20		9494		strnc Mult Tr Crprtn										18	11/4/20 15:54
55	7/13/20		9596		strnc Mult Tr Crprtn										18	11/4/20 15:54
56
Payment

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F25:G55,E56:E1048576	Cell Value	<0	text	NO
A5:R55	Expression	=$O4<>$O5	text	NO
B56:B1048576,C25:C55	Cell Value	duplicates	text	NO
D5:D55	Cell Value	duplicates	text	NO
C5:C55	Cell Value	duplicates	text	NO
K5:K55	Cell Value	duplicates	text	NO

Cells with Data Validation
Cell	Allow	Criteria
F5:F55	List	=$AH$1:$AH$6

 

[Joe4]

The issue is that your columns are all different than the ones in your example (which columns the key data exists in).
I purposely documented my code to tell you exactly what is going on, so you should be able to make those column adjustments yourself.
Have you tried doing that? If not, please try now. It is good practice and should help you understand exactly what the code is doing.

If you still run into trouble, post your updated code, and I will help you fix it up.