Data Lookup In A Table

[jim061]

HELP

I'm working on creating a schedule for a ball hockey league. I have the scheduling part complete, the only part left is a workbook used where you just have to enter the team name and the schedule pops out.

The initial VLOOKUP command, I could create no problem. The only problem is that each team plays 15 times, and the VLOOKUP will only pickup the first time the name shows up. What command do I use in the second line to find the second time the name comes up, and in the third line to find the third time the name comes up, etc.

Thank you to anyone who can help me, it will be greatly appreciated.

 

[Aladin Akyurek]

Try to provide a small sample along with the desired results...

 

[jim061]

Here's just a general outline. There is tonnes of coding done in prior worksheets that will generate the entire schedule, but I've just plugged in a team-name four times and only 12 timeslots to give a general idea (I've got this coding fine, probably not the most efficient, but I understand it, lol). The actual schedule has 1200 available timeslots for 15 games each for 120 teams (so much more massive, making the simply sorts, much more time consuming, and confusing, I'm trying to make the program very user-friendly for people with limited computer skills).

.............DATE..............GAME TIME...RINK.....HOME.....AWAY.....REFS

Monday..September 4......5:30-6:30....1........
Monday..September 4......6:30-7:30....1........Stars
Monday..September 4......7:30-8:30....1......................Stars
Monday..September 4......8:30-9:30....1........
Monday..September 4....9:30-10:30....1........
Monday..September 4...10:30-11:30....1........
Monday..September 4......5:30-6:30....2........
Monday..September 4......6:30-7:30....2........
Monday..September 4......7:30-8:30....2........
Monday..September 4......8:30-9:30....2........Stars
Monday..September 4....9:30-10:30....2......................Stars
Monday..September 4...10:30-11:30....2........

(Note, for above, for some reason, the spaces that I have typed do not show up, so I've had to put in the dots)

Because the final schedule will be absolutely massive, what I want is to have a worksheet where all the person using the program has to do is type the team name and the schedule comes up.

So, what I've done, I've got a place at the top of the page to put the team name. On the schedule page, I've added hidden columns (width=0) to the right of the completed schedule so I could use vlookup commands.

So, each line has a bunch of "VLOOKUP" commands, one to pull the day of the week, one to pull the date, one to pull the game time, one to pull the rink #, one to pull the home team and one to pull the away team. The only problem is, the VLOOKUP will only pull the team name the first time. So, QUESTION #1, how do I write a command to pull the second one, and the third one, and the fourth one, etc?

As well, I've had to use two different VLOOKUP commands to pull from each of the HOME and the AWAY columns. So, QUESTION #2, is there a command to search for the data in multiple columns, as opposed to just the first column?

I appreciate any help I can get on this matter. Question #1 is the more urgent, I can live without an answer to Question #2.

 

[Domenic]

Assuming that A2:G13 contains the data, Column E contains the Home team, Column F contains the Away team, and I2 contains the team of interest, such as Stars, try the following...

J2:

=SUMPRODUCT((E2:E13=I2)+(F2:F13=I2))

K2, copied down and across:

=IF(ROWS(K$2:K2)<=$J$2,INDEX(A$2:A$13,SMALL(IF(($E$2:$E$13=$I$2)+($F$2:$F$13=$I$2),ROW(A$2:A$13)-ROW(A$2)+1),ROWS(K$2:K2))),"")

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[jim061]

I wasn't able to get it to work exactly how you had it. But it gave me an idea on how to do it. To the left of the schedule, I use a sumproduct from whatever line I'm on to the bottom line of the schedule (like E2:E$13 on the home side on what you suggested, and F2:F$13 on the visitors side). Then, I create a bunch of if statements. The next column states that if the product is less than than the row below it, that means the name must appear on that line, so then using the same if statement across, it copies the rows that I need. I also asign a value to each row that has data (#s 1-15), then do a vlookup for the #1, #2, #3, etc on the seperate worksheet and pull up the relating data.

Thanks for your help.

 

[Domenic]

If I understood you correctly, you'd like to search the Home and Away columns for the team of interest, such as Star, and return all corresponding rows. Is this correct? If so, my solution will provide the desired result. If this is the case, did you confirm the second formula with CONTROL+SHIFT+ENTER, not just ENTER?

 

[jim061]

Ah, I think that was the problem. I didn't do the control+shift+enter on the second formula because the answer already came up immediately.

For future reference, what exactly does your coding "say" in Leyman's terms, and why the Control+Shift+Enter, what does that do?

Thanks for your help