Cell value linked to wrong cell - formulas wrong?

rob1234

New Member
Joined
Aug 22, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKL
1Score 1Score 2Total scoreClientresultrankrankClientresult
2Client
3Client A65-1550Client A50131Client P154
4Client B215778Client B78122Client F150
5Client C6557122Client C12293Client D 147
6Client D 7968147Client D 14734Client M146
7Client E523789Client E89115Client N145
8Client F7476150Client F15026Client K130
9Glient G3291123Glient G12387Client J129
10Client H9222114Client H114108Glient G154
11Client I43-45-2Client I-2149Client C122
12Client J8346129Client J129710Client H114
13Client K7357130Client K130611Client E89
14Client L61-94-33Client L-331512Client B78
15Client M8264146Client M146413Client A50
16Client N9055145Client N145514Client I-2
17Client O2-64-62Client O-621615Client L-33
18Client P7579154Client P154116Client O-62
19
20
21Why is Client G's value in cell L10 always the same as the Client P figure in L3?
22The value in Cell L10 should be 123
Sheet1
Cell Formulas
RangeFormula
F3:F18F3=UNIQUE(A3:A18,FALSE,FALSE)
G3:G18G3=D3
H3:H18H3=RANK.EQ(D3,$D$3:$D$18,0)
K3:K18K3=OFFSET($F$2,MATCH(J3,$H$3:$H$18,0),0)
L3:L18L3=VLOOKUP($K3,$F$3:$H$18,2)
D3:D18D3=SUM(B3:C3)
Dynamic array formulas.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Muhammad_Usman

Active Member
Joined
Dec 9, 2015
Messages
367
Please Replace Glient G to Client Name in F9 and G 10 .. It will work perfectly


26-08-21 Exp.xlsx
ABCDEFGHIJKL
2Score 1Score 2Total scoreClientresultrankrankresult
32Client
43Client A65-1550Client A50131Client P154
54Client B215778Client B78122Client F150
65Client C6557122Client C12293Client D 147
76Client D7968147Client D14734Client M146
87Client E523789Client E89115Client N145
98Client F7476150Client F15026Client K130
109Glient G3291123Client G12387Client J129
1110Client H9222114Client H114108Client G123
1211Client I43-45-2Client I-2149Client C122
1312Client J8346129Client J129710Client H114
1413Client K7357130Client K130611Client E89
1514Client L61-94-33Client L-331512Client B78
1615Client M8264146Client M146413Client A50
1716Client N9055145Client N145514Client I-2
1817Client O2-64-62Client O-621615Client L-33
1918Client P7579154Client P154116Client O-62
Sheet11
Cell Formulas
RangeFormula
L4:L19L4=VLOOKUP($K4,$G$4:$I$19,2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
L4:L19Cell ValueduplicatestextNO
 
Solution

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,596
Office Version
  1. 365
Platform
  1. Windows
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
FGHIJKL
2ClientresultrankrankClientresult
3
4Client A50131Client P154
5Client B78122Client F150
6Client C12293Client D 147
7Client D 14734Client M146
8Client E89115Client N145
9Client F15026Client K130
10Glient G12387Client J129
11Client H114108Glient G123
12Client I-2149Client C122
13Client J129710Client H114
14Client K130611Client E89
15Client L-331512Client B78
16Client M146413Client A50
17Client N145514Client I-2
18Client O-621615Client L-33
19Client P154116Client O-62
VLOOKUP
Cell Formulas
RangeFormula
L4:L19L4=VLOOKUP($K4,$F$4:$H$19,2,0)
 

Forum statistics

Threads
1,176,502
Messages
5,903,425
Members
435,029
Latest member
excelvbanoob420

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
Top