lookup match formula error

T

TCM1770

Board Regular
Joined
Feb 6, 2005
Messages
121
Office Version
  1. 365
I created a workbook to track stats for a card game. I am using a formula using a lookup (of the team) referencing the weekly results using a match function and the results are pulling from the incorrect week. This also references named ranges. Can someone take a look at the formula and let me know what went wrong. It worked all of last year but somehow got corrupt this season.
From the legue schedule based on the number of teams it creates the schedule for the divisions. ''=VLOOKUP($A40,CLUBS,2) Keeps the running completed schedule. The results are calculated from the WKLY_RSLTS sheet 'VLOOKUP(H40,WKLY_RSLTS!$B$3:$O$16,MATCH(TeamCalc!$T$39,WKLY_RSLTS!$B$2:$O$2),FALSE)
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Can you post some rows of your sample data.
 
Upvote 0
Sufiyan97 said:
Can you post some rows of your sample data.
Click to expand...
I am having some issues with the mini sheet. is there any way I can send you a file? I would greatly appreciate it since the lookups and matches uses 3 worksheets.
 
Upvote 0
See if this solve xl2bb issue

www.mrexcel.com

xl2bb

Hi all, I have just upgraded to Windows 11 and dowloaded xl2bb, however I get a warning message that Microsoft has disabled macros due to the certificate not being trusted. Therefore xl2bb does not work. Any advice to solve this issue would be greatly appreciated. Cheers, Dave.
www.mrexcel.com www.mrexcel.com
 
Upvote 0
2023_Spring_Cards.Excel.xlsm
ABCDEFGHIJKLMNOPQRSTUVW
81WEEK 1WEEK 2WEEK 3Week 1 - JAN 16Week 2 - JAN 23Week 3 - JAN 30WK1WK2WK3TTL
821132151ORLANDO/LAURA 5MORGAN REGAN 2TYLER / BRI0ORLANDO/LAURA 7JACKIE/SANDYM/UORLANDO/LAURA M/U77#VALUE!#VALUE!
8321214342TYLER / BRI5PAUL/MARK2DON/SUE 0JOE /RICK7CHRIS /SHARON5TYLER / BRI277721
8431113463JOE /RICK3DARLENE /TOMMY4MORGAN REGAN 5CHRIS /SHARON2BRUNO/CHRIS 6JOE /RICK177721
85410125137CHRIS /SHARON5 AMY /PAIGE 2PAUL/MARK3JACKIE/SANDY4MORGAN REGAN 3JASON /SKIP 477721
8659611812JACKIE/SANDY5BUDDY/THERESA 2BRUNO/CHRIS 3DARLENE /TOMMY4GENE/LINDA 3PAUL/MARK477721
8768107911BRUNO/CHRIS 5GENE/LINDA 2 AMY /PAIGE 3JASON /SKIP 4BUDDY/THERESA 3DARLENE /TOMMY477721
88147981014DON/SUE 2JASON /SKIP 5BUDDY/THERESA 2GENE/LINDA 5 AMY /PAIGE 5DON/SUE 277721
89
90WEEK 4WEEK 5WEEK 6Week 4 - FEB 6Week 5 -FEB 13Week 6 - FEB 20WK4WK5WK6TTL
918111113GENE/LINDA 2ORLANDO/LAURA 5ORLANDO/LAURA 5DARLENE /TOMMY1ORLANDO/LAURA 0JOE /RICK076013
9227102213TYLER / BRI4JASON /SKIP 3 AMY /PAIGE 3TYLER / BRI4TYLER / BRI0MORGAN REGAN 077014
9393123411BUDDY/THERESA 5JOE /RICK2PAUL/MARK6JOE /RICK2CHRIS /SHARON0DARLENE /TOMMY078015
944584510CHRIS /SHARON3JACKIE/SANDY4GENE/LINDA 2CHRIS /SHARON3JACKIE/SANDY0 AMY /PAIGE 075012
95121157612PAUL/MARK6DARLENE /TOMMY1JACKIE/SANDY4JASON /SKIP 3BRUNO/CHRIS 0PAUL/MARK077014
96614141387BRUNO/CHRIS 3DON/SUE 4DON/SUE 4MORGAN REGAN 4GENE/LINDA 0JASON /SKIP 078015
97131096149MORGAN REGAN 4 AMY /PAIGE 3BUDDY/THERESA 5BRUNO/CHRIS 3DON/SUE 0BUDDY/THERESA 078015
98
99WEEK 7WEEK 8WEEK 9Week 7 - FEB 27Week 8 - MAR 6Week 9 - MAR 13WK7WK8WK9TTL
1006191121BRUNO/CHRIS 6ORLANDO/LAURA M/UBUDDY/THERESA 5ORLANDO/LAURA 5PAUL/MARK0ORLANDO/LAURA 0#VALUE!100#VALUE!
1011422582DON/SUE 2TYLER / BRI2TYLER / BRI4JACKIE/SANDY4GENE/LINDA 0TYLER / BRI048012
1024373103CHRIS /SHARON5JOE /RICK1JASON /SKIP 3JOE /RICK2 AMY /PAIGE 0JOE /RICK065011
1031354694MORGAN REGAN 3JACKIE/SANDYM/UCHRIS /SHARON3BRUNO/CHRIS 3BUDDY/THERESA 0CHRIS /SHARON0#VALUE!60#VALUE!
104711813145JASON /SKIP 4DARLENE /TOMMY4GENE/LINDA 2MORGAN REGAN 4DON/SUE 0JACKIE/SANDY086014
105108101167 AMY /PAIGE 5GENE/LINDA 3 AMY /PAIGE 3DARLENE /TOMMY1BRUNO/CHRIS 0JASON /SKIP 084012
10612912141311PAUL/MARK4BUDDY/THERESA 3PAUL/MARK6DON/SUE 4MORGAN REGAN 0DARLENE /TOMMY0710017
107
108WEEK 10WEEK 11WEEK 12Week 10 -MAR 20Week 11 - MAR 27Week 12 - APR 3WK10WK11WK12TTL
1091414171DON/SUE 2ORLANDO/LAURA M/UCHRIS /SHARON3ORLANDO/LAURA 5JASON /SKIP 0ORLANDO/LAURA 0#VALUE!80#VALUE!
1102112326TYLER / BRI2DARLENE /TOMMY4TYLER / BRI4JOE /RICK2TYLER / BRI0BRUNO/CHRIS 066012
11113351153MORGAN REGAN 3JOE /RICK1JACKIE/SANDY4DARLENE /TOMMY1JACKIE/SANDY0JOE /RICK04509
112412136414CHRIS /SHARON5PAUL/MARK4MORGAN REGAN 4BRUNO/CHRIS 3CHRIS /SHARON0DON/SUE 097016
11358127811JACKIE/SANDYM/UGENE/LINDA 3PAUL/MARK6JASON /SKIP 3GENE/LINDA 0DARLENE /TOMMY0#VALUE!90#VALUE!
114610148913BRUNO/CHRIS 6 AMY /PAIGE 5DON/SUE 4GENE/LINDA 2BUDDY/THERESA 0MORGAN REGAN 0116017
115971091210BUDDY/THERESA 3JASON /SKIP 4 AMY /PAIGE 3BUDDY/THERESA 5PAUL/MARK0 AMY /PAIGE 078015
116
117WEEK 13Week 13 -APR 10Playoff APR 17WK13WK14WK15TTL
118101 AMY /PAIGE 5ORLANDO/LAURA M/U#VALUE!00#VALUE!
11992BUDDY/THERESA 3TYLER / BRI20
12083GENE/LINDA 3JOE /RICK10
12147CHRIS /SHARON5JASON /SKIP 40
12265BRUNO/CHRIS 6JACKIE/SANDYM/U0
1231411DON/SUE 2DARLENE /TOMMY40
1241312MORGAN REGAN 3PAUL/MARK40
TeamCalc
Cell Formulas
RangeFormula
T82:T88,T118,T109:T115,T100:T106,T91:T97T82=I82+K82
U82:U88,U118,U109:U115,U100:U106,U91:U97U82=M82+O82
V82:V88,V118,V109:V115,V100:V106,V91:V97V82=Q82+S82
W82:W88,W118:W124,W109:W115,W100:W106,W91:W97W82=SUM(T82:V82)
L91:L97,L109:L115,L100:L106L91=VLOOKUP($C91,CLUBS,2)
M91:M97,O91:O97,M109:M115,O109:O115,M100:M106,O100:O106M91=VLOOKUP(L91,WKLY_RSLTS!$B$3:$O$16,MATCH(TeamCalc!$U$47,WKLY_RSLTS!$B$2:$O$2),FALSE)
N91:N97,N109:N115,N100:N106N91=VLOOKUP($D91,CLUBS,2)
P91:P97,P109:P115,P100:P106P91=VLOOKUP($E91,CLUBS,2)
Q91:Q97,S91:S97,Q109:Q115,S109:S115,Q100:Q106,S100:S106Q91=VLOOKUP(P91,WKLY_RSLTS!$B$3:$O$16,MATCH(TeamCalc!$V$47,WKLY_RSLTS!$B$2:$O$2),FALSE)
R91:R97,R109:R115,R100:R106R91=VLOOKUP($F91,CLUBS,2)
H91:H97,H118:H124,H109:H115,H100:H106H91=VLOOKUP($A91,CLUBS,2)
J91:J97,J118:J124,J109:J115,J100:J106J91=VLOOKUP($B91,CLUBS,2)
I100:I106,K100:K106,I118:I124,K118:K124,I109:I115,K109:K115I100=VLOOKUP(H100,WKLY_RSLTS!$B$3:$O$16,MATCH(TeamCalc!$T$47,WKLY_RSLTS!$B$2:$O$2),FALSE)
Named Ranges
NameRefers ToCells
CClubs=WKLY_RSLTS!$B$3:$O$16K118:K124, I118:I124, S109:S115, Q109:Q115, O109:O115, M109:M115, K109:K115, I109:I115, S100:S106, Q100:Q106, O100:O106, M100:M106, K100:K106, I100:I106, S91:S97, Q91:Q97, O91:O97, M91:M97
CLUBS=TEAMS!$A$1:$B$15H91:H97, J91:J97, L91:L97, N91:N97, P91:P97, R91:R97, H100:H106, J100:J106, L100:L106, N100:N106, P100:P106, R100:R106, H109:H115, J109:J115, L109:L115, N109:N115, P109:P115, R109:R115, H118:H124, J118:J124
CWeeks=WKLY_RSLTS!$B$2:$O$2K118:K124, I118:I124, S109:S115, Q109:Q115, O109:O115, M109:M115, K109:K115, I109:I115, S100:S106, Q100:Q106, O100:O106, M100:M106, K100:K106, I100:I106, S91:S97, Q91:Q97, O91:O97, M91:M97
 
Upvote 0
in which column/cells you are getting incorrect results,
Can you also post data from the sheets WKLY_RSLTS and TEAMS.
 
Upvote 0
when i entered the results in the WKLY_RSLTS based on the formula i expected the formula to update the week of play in the TeamCalc sheet. i have uploaded the mini sheets you requested. thank you for your help.
2023_Spring_Cards.xlsm
ABCDEFGHIJKLMNOP
1CLUBS
2T#PlayerWK1 WK2 WK3 WK4 WK5 WK6 WK7 WK8 WK9 WK10WK11WK12WK13TTL
31ORLANDO/LAURA 57M/U517
46BRUNO/CHRIS 536317
57JASON/SKIP 544316
64CHRIS/SHARON525315
712PAUL/MARK234615
813MORGAN/REGAN 253414
93JOE/RICK371213
105JACKIE/SANDY54M/U413
1110 AMY/PAIGE 235313
1211DARLENE/TOMMY444113
138GENE/LINDA 253212
149BUDDY/THERESA 223512
152TYLER/BRI502411
1614DON/SUE 20248
WKLY_RSLTS
Cell Formulas
RangeFormula
P3:P16P3=SUM(C3:O3)
 
Upvote 0
here is the Teams sheet
2023_Spring_Cards.xlsm
ABCDE
1CLUBSTeamDIAMONDSTeam
21ORLANDO/LAURA 1ERIC/MIKE
32TYLER/BRI2KEN/DON
43JOE/RICK3RYAN/ERIC
54CHRIS/SHARON4STEVE/BEN
65JACKIE/SANDY5DENNIS/JILL
76BRUNO/CHRIS 6MATT/SARAH
87JASON/SKIP 7KELLY/LISA
98GENE/LINDA 8TERRY/JOANNE
109BUDDY/THERESA 9BRIDGET/SAM
1110 AMY/PAIGE 10PAULIE/TONY
1211DARLENE/TOMMY11TONY/BOB
1312PAUL/MARK12JENN/OLGA
1413MORGAN/REGAN 13KRISTEN/BECKY
1514DON/SUE 14BOB/CAROL
TEAMS
 
Upvote 0
Named Ranges
NameRefers ToCells
CClubs=WKLY_RSLTS!$B$3:$O$16K118:K124, I118:I124, S109:S115, Q109:Q115, O109:O115, M109:M115, K109:K115, I109:I115, S100:S106, Q100:Q106, O100:O106, M100:M106, K100:K106, I100:I106, S91:S97, Q91:Q97, O91:O97, M91:M97
CLUBS=TEAMS!$A$1:$B$15H91:H97, J91:J97, L91:L97, N91:N97, P91:P97, R91:R97, H100:H106, J100:J106, L100:L106, N100:N106, P100:P106, R100:R106, H109:H115, J109:J115, L109:L115, N109:N115, P109:P115, R109:R115, H118:H124, J118:J124
CWeeks=WKLY_RSLTS!$B$2:$O$2K118:K124, I118:I124, S109:S115, Q109:Q115, O109:O115, M109:M115, K109:K115, I109:I115, S100:S106, Q100:Q106, O100:O106, M100:M106, K100:K106, I100:I106, S91:S97, Q91:Q97, O91:O97, M91:M97



The data is empty in mini sheet for above names ranges.
 
Upvote 0
not sure what you mean by data is empty? i may have deleted the data because i had to publish the results.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Soccer League Workbook
Replies
1
Views
233
hagia_sofia
hagia_sofia
J
  • Question Question
Index, Match, Large based on two Criteria
Replies
3
Views
114
Fluff
Fluff
D
  • Solved
INDEX-MATCH with a vertical lookup
Replies
4
Views
583
Darren_workforce
D
L
  • Solved
Lookup and retrieve value based on table code (2)
Replies
3
Views
461
Peter_SSs
Peter_SSs
S
  • Question Question
Creating a random sequence of items from a weighted list with no duplicates
Replies
6
Views
326
shg
shg

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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