1

I am working with some sports data and am attempting to dynamically pull game info into another excel sheet. I have created an excel formula that does mostly what I want but I want to see if I can change the behavior slightly. Here are some screenshots of the data and then I will explain what I am trying to achieve.

This first screenshot is a small sample size of some of the data I am working with. I have Air Force in bold as that is the example team that I will be using for the data pull. Schedule Data

Here is where I am pulling the data on another sheet in excel. The table on the left is the result I currently get, but the table on the right is the table format I am trying to achieve. Cell A1 is just used to run a compare to the "Schedule" sheet. Formula Data Pull

Here is the formula I am currently using:

=IFERROR(INDEX(Schedule!$A$1:$I$866,MATCH((AGGREGATE(15,3,((Schedule!$F$1:$I$866=$A$1)/(Schedule!$F$1:$I$866=$A$1)*ROW(Schedule!$F$2:$I$866))-ROW(Schedule!$F$1),ROWS($O$2:O2)))-1,Schedule!$A:$A,0),COLUMN(B$1)),"")

So the changes I know that will need to be done to make the 2nd table possible are the IF statement which I have one created and then to do the increment for the [k] value of the AGGREGATE formula only if IF statement is true.

Here is the logic I am doing for the IF statement:

=IF(XLOOKUP(1,((Schedule!$B$2:$B$30=$I9)*((Schedule!$F$2:$F$30=$A$1)+(Schedule!$G$2:$G$30=$A$1))),Schedule!$B$2:$B$30,"")=$I9,*PULL THE DATA*,*LEAVE DATA BLANK*)

So the current [k] increment value being used is ROWS($O$2:O2) which works fine but since 'Air Force' is no present during week 3 is just pulls the next available which is week 4.

How can I go about instead keeping a more static value that increments by 1 when the formula is actually ran?

I have changed the formula to this which then allows me to have static weeks and then pull starting from DATE rather than WK:

=IF(XLOOKUP(1,((Schedule!$B$2:$B$30=$I9)*((Schedule!$F$2:$F$30=$A$1)+(Schedule!$G$2:$G$30=$A$1))),Schedule!$B$2:$B$30,"")=$I9,IFERROR(INDEX(Schedule!$A$1:$I$30,MATCH((AGGREGATE(15,3,((Schedule!$F$1:$I$30=$A$1)/(Schedule!$F$1:$I$30=$A$1)*ROW(Schedule!$F$2:$I$30))-ROW(Schedule!$F$1),ROWS($O$2:O2)))-1,Schedule!$A:$A,0),COLUMN(C$1)),""),"")

So when week 3 comes around, since no data will be pulled for week 3, I need the [k] value of the AGGREGATE function not to increment.

Maybe I am going about this the wrong way, but this is what I was able to get close enough of my final goal of the data pull.

Any help is appreciated. Thank you.

UPDATE

Due to the weeks indeed being a static known data, I was able to adapt and use bugdrown's suggestion which looks to be working flawlessly.

=XLOOKUP($B2&$A$1,Schedule!$B$2:$B$30&Schedule!$F$2:$F$30,Schedule!$C$2:$G$30,XLOOKUP($B2&$A$1,Schedule!$B$2:$B$30&Schedule!$G$2:$G$30,Schedule!$C$2:$G$30,"",0),0)

Thank you!

Improve this question
3
  • Please do not upload images of code/data/errors. Commented Aug 23, 2024 at 22:39
  • Which version of Excel are you using? Commented Aug 24, 2024 at 16:39
  • Currently using version 2407 Commented Aug 24, 2024 at 19:03

1 Answer 1

Reset to default
1

Assumptions:
A.) You want to return a complete schedule (home and away games) for a single team. B.) "Bye" weeks should a show blank row.

Since the number of weeks in the season are known, set up your lookup table with the WK column filled in: =SEQUENCE(18)

In the lookup formula, set the lookup_value argument to a concatenation of WK and the team name. We'll nest XLOOKUP() formulas so that if an AWAY game doesn't exist for a WK, another XLOOKUP() will look up the concatenation of WK and team name in the HOME column, otherwise, return an empty string.

In my workbook, I named the sheet with the complete schedule as MasterSchedule and the schedule for the criterion team as TeamSchedule. I've located the team lookup_value in cell TeamSchedule!$I$2 (currently set to Air Force).

With WK data for the season filled in TeamSchedule!$A2:$19 using: =SEQUENCE(18) in TeamSchedule!$A$2, enter the following formula in TeamSchedule!B2 and drag it down through TeamSchedule!B19:

 =XLOOKUP($A2&$I$2,MasterSchedule!$B$2:$B$30&MasterSchedule!$F$2:$F$30,MasterSchedule!$C$2:$G$30,
          XLOOKUP($A2&$I$2,MasterSchedule!$B$2:$B$30&MasterSchedule!$G$2:$G$30,MasterSchedule!$C$2:$G$30,"",0)
  ,0)
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I was able to adapt this to my spreadsheet and this works great! Thank you very much. Getting rid of the AGGREGATE function has made this much cleaner as well.
@Link Glad this helped. Thank you for the points and good luck with your project.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.