Data Lookup In A Table

jim061

New Member
Joined
Aug 28, 2006
Messages
4
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.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,247
Messages
6,177,404
Members
452,774
Latest member
Macca1962

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