Cell value linked to wrong cell - formulas wrong?

[rob1234]

Hi there, can anyone help me?
Why is Client G's value in Cell L10 always showing the same as Client P's value in L3? The correct value for cell L10 should be 123.

football table example of auto ranking data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		Score 1	Score 2	Total score		Client	result	rank		rank	Client	result
2	Client
3	Client A	65	-15	50		Client A	50	13		1	Client P	154
4	Client B	21	57	78		Client B	78	12		2	Client F	150
5	Client C	65	57	122		Client C	122	9		3	Client D	147
6	Client D	79	68	147		Client D	147	3		4	Client M	146
7	Client E	52	37	89		Client E	89	11		5	Client N	145
8	Client F	74	76	150		Client F	150	2		6	Client K	130
9	Glient G	32	91	123		Glient G	123	8		7	Client J	129
10	Client H	92	22	114		Client H	114	10		8	Glient G	154
11	Client I	43	-45	-2		Client I	-2	14		9	Client C	122
12	Client J	83	46	129		Client J	129	7		10	Client H	114
13	Client K	73	57	130		Client K	130	6		11	Client E	89
14	Client L	61	-94	-33		Client L	-33	15		12	Client B	78
15	Client M	82	64	146		Client M	146	4		13	Client A	50
16	Client N	90	55	145		Client N	145	5		14	Client I	-2
17	Client O	2	-64	-62		Client O	-62	16		15	Client L	-33
18	Client P	75	79	154		Client P	154	1		16	Client O	-62
19
20
21			Why is Client G's value in cell L10 always the same as the Client P figure in L3?
22			The value in Cell L10 should be 123
Sheet1

Cell Formulas
Range	Formula
F3:F18	F3	=UNIQUE(A3:A18,FALSE,FALSE)
G3:G18	G3	=D3
H3:H18	H3	=RANK.EQ(D3,$D$3:$D$18,0)
K3:K18	K3	=OFFSET($F$2,MATCH(J3,$H$3:$H$18,0),0)
L3:L18	L3	=VLOOKUP($K3,$F$3:$H$18,2)
D3:D18	D3	=SUM(B3:C3)
Dynamic array formulas.

 

[Muhammad_Usman]

Please Replace Glient G to Client Name in F9 and G 10 .. It will work perfectly

26-08-21 Exp.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
2	Score 1	Score 2	Total score		Client	result	rank		rank	result
3	2	Client
4	3	Client A	65	-15	50		Client A	50	13	1	Client P	154
5	4	Client B	21	57	78		Client B	78	12	2	Client F	150
6	5	Client C	65	57	122		Client C	122	9	3	Client D	147
7	6	Client D	79	68	147		Client D	147	3	4	Client M	146
8	7	Client E	52	37	89		Client E	89	11	5	Client N	145
9	8	Client F	74	76	150		Client F	150	2	6	Client K	130
10	9	Glient G	32	91	123		Client G	123	8	7	Client J	129
11	10	Client H	92	22	114		Client H	114	10	8	Client G	123
12	11	Client I	43	-45	-2		Client I	-2	14	9	Client C	122
13	12	Client J	83	46	129		Client J	129	7	10	Client H	114
14	13	Client K	73	57	130		Client K	130	6	11	Client E	89
15	14	Client L	61	-94	-33		Client L	-33	15	12	Client B	78
16	15	Client M	82	64	146		Client M	146	4	13	Client A	50
17	16	Client N	90	55	145		Client N	145	5	14	Client I	-2
18	17	Client O	2	-64	-62		Client O	-62	16	15	Client L	-33
19	18	Client P	75	79	154		Client P	154	1	16	Client O	-62
Sheet11

Cell Formulas
Range	Formula
L4:L19	L4	=VLOOKUP($K4,$G$4:$I$19,2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L4:L19	Cell Value	duplicates	text	NO

 

[Peter_SSs]

Why is Client G's value in Cell L10 always showing the same as Client P's value in L3? The correct value for cell L10 should be 123.

Because your lookup is not looking for an exact match and your lookup table is not in alphabetical order.

Try changing the L4 formula to the following and copy down

=VLOOKUP($K4,$F$4:$H$19,2,0)

21 08 26.xlsm
	F	G	H	I	J	K	L
2	Client	result	rank		rank	Client	result
3
4	Client A	50	13		1	Client P	154
5	Client B	78	12		2	Client F	150
6	Client C	122	9		3	Client D	147
7	Client D	147	3		4	Client M	146
8	Client E	89	11		5	Client N	145
9	Client F	150	2		6	Client K	130
10	Glient G	123	8		7	Client J	129
11	Client H	114	10		8	Glient G	123
12	Client I	-2	14		9	Client C	122
13	Client J	129	7		10	Client H	114
14	Client K	130	6		11	Client E	89
15	Client L	-33	15		12	Client B	78
16	Client M	146	4		13	Client A	50
17	Client N	145	5		14	Client I	-2
18	Client O	-62	16		15	Client L	-33
19	Client P	154	1		16	Client O	-62
VLOOKUP

Cell Formulas
Range	Formula
L4:L19	L4	=VLOOKUP($K4,$F$4:$H$19,2,0)