Find reference cell data in another worksheet, return a cell in a different row and column

Welcome to the forum.

My first recommendation would be to rearrange the data in 'Master Schedule'.

If you are not aware of it Excel is a row-centric application. Formulas work best with that in mind.

	B	C	D
2	Week 1	Team A	Team B
3		Team C	Team D
4		Team E	Team F
5	Week 2	Team D	Team E
6		Team F	Team A
7		Team B	Team C
8	Week 3	Team F	Team D
9		Team E	Team B
10		Team A	Team C
11	Week 4	Team B	Team D
12		Team F	Team C
13		Team A	Team E
14	Week 5	Team D	Team A
15		Team C	Team E
16		Team B	Team F

Then array enter this formula in B4 of 'Week 1' and fill down to B5. Copy and those and paste into cells B7 and B10. The other cells will fill in.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

Since you are apparently familiar with grouped sheets you can do all the preceding formula steps that way, too.

I failed to mention that I also removed the "vs" columns. They complicate the column argument in INDEX. They are also unnecessary.

Thanks for the feedback. Glad it does the job.

Originally Posted by blue_shadow_

Thanks! That is way more complicated than I thought it would be. Would it be possible to get a short breakdown of the formula? I'll be wanting to cross-reference more than just the schedule for the league, so I'll be looking to reconstruct this for use elsewhere.

I will do my best on that. It will require some time for me to organize my thoughts to words. I also have some other projects pending.

In the meantime I apologize for an oversight on my part. The copy and paste steps are unnecessary with this formula array entered in B4 and filled down. All I did here is wrap the original formula in IFERROR.

Formula:

Please Login or Register  to view this content.

That will also format the blank rows with borders. You may want to reformat those. (Format painter is our friend. )

I'll be back with the breakdown.

I noticed another oversight on my part. I had assumed out of habit that the formula had to be array committed. It does not. A simple Enter will work.

My apologies again.

RE: the short breakdown.

I don't think I can do it. We're limited to 15,000 characters per post.

I also don't know how familiar you are with functions. If you are fairly new to this the formula is not a good one to start with. My apologies if I have that wrong. In any event all limitations are mine.

Never-the-less don't be intimidated.

As a preliminary or alternative I recommend self study. Studying this may take considerable time. That's normal. Been there ... done that ... still doing it.

Here are some starting points:

1. Make a back up copy of your file and use that for practicing/experimenting.
2. If you are not familiar with grouped sheets study up on that. This will be more meaningful and less distracting if you use that. (All formulas can be entered into all sheets at once.)
3. Isolate the CEILING(ROWS($4:4)/3,1) part and put it in K4:K12. Notice the patterns. Exam each sheet. For details on CEILING read the help file as you study this.
4. Isolate the MOD(ROWS($4:4)-1,3)+1 part and do likewise L4:L12. Patterns ... details ... help. Exam each sheet.
5. Isolate the MATCH($B$2,'Master Schedule'!$B$2:$B$100,0) part from the formula and copy it to J4:J12 with sheets grouped. If you are not familiar with how MATCH works study that also.
6. Also notice that reference to $B$2 does not include a sheet name. Each sheet references its own unique $B$2.

Begin to add in the remaining parts to MATCH in J4:J12. Do each of these steps on all the sheets grouped:

1. =MATCH($B$2,'Master Schedule'!$B$2:$B$100,0)-1. Exam each sheet. So far nothing to write home about ... just a change of number.
2. Now change that to =MATCH($B$2,'Master Schedule'!$B$2:$B$100,0)-1+K4 Examine each sheet. Things get very interesting.

Play with numbers in each of those parts. Experiment until you get the idea of the affects and effects.

With that understanding notice how each part ... the modified MATCH in the row argument of INDEX and the MOD part in the column argument of INDEX ... operates inside of INDEX.

I hope this hasn't overwhelmed.

If you get stuck post back. I have subscription to this thread. Others are reading as well.

Has any of this helped?

This just tests if there's an error with function, and allows for a custom error message if there does happen to be one. The error message, if I wanted to change it, would go in the "" at the end of the formula. Not really an integral part of the formula itself, just an additional check.

That is correct.

INDEX() in this case seems to be under the reference version, not the array.

Yes.

'Master Schedule'!$C$2:$D$100, then, would be the range of cells to look within.

Right again.

I can't tell what the -1 is for, unless it's to put an inherent modifier into the MATCH location.

Looking at the breakdown, the only thing I can think of is that +CEILING(ROWS($4:4)/3,1) seems to be a modifier for the location of MATCH.

Exactly right on both counts.

I'm not sure why CEILING is used here, why division of the ROWS count is necessary though.

That assures the result of the divisions will repeat that many times. That is my "default" method of repeating and incrementally returning row numbers to pass to INDEX. I also use ROWS because it is a handy way to increment. There are other methods. In column K try changing the divisor from 3 to something else. Note the affect.

You are right. There is a "rounding" function at work here. Try changing the second argument to something other than 1. Note the affects.

MOD(ROWS($4:4)-1,3)+1 seems to be taking the place of the second MATCH in INDEX MATCH MATCH that's readily found online, but again, I'm not sure exactly what its purpose directly here is.

Yes it does take the place of what is ordinarily another MATCH. The MOD in the column argument permits cycling through the column numbers ... 1,2,3 ... 1,2,3 ... etc. with each row change.

Since there is no column 3. INDEX returns #REF! errors. IFERROR covers those and returns "". Cycling 1,2,3 accounts for intended blanks every third row. If you decided to dispense with the blank rows the MOD would need 2 in it's second argument to cycle 1,2...1,2...1,2 etc.

the questions I have remaining are about the -1 portion of MATCH, +CEILING(), and MOD()+1. I can see what CEILING and MOD are returning via the tests you suggested, but I'm not sure what those values are doing within the formula overall.

INDEX expects numbers understood to reference row and column "coordinates". INDEX does not care how those numbers are derived. While MATCH is often used for those purposes that is not always workable. We have to invent other ways.

I suggest playing with both the CEILING and MOD changing numbers and noticing the repeating patterns. Try MOD(ROWS($4:4),3) for starters. See how it cycles 1,2,0...1,2,0. That cycling looks useful if it wasn't for all those cussed 0s! Next try MOD(ROWS($4:4)-1,3). The cycle is now 0,1,2...0,1,2. Hmmm. The 0s have moved. Now add 1 to that. When put into the column argument it does it's magic cycling through the columns with each row change.

You consider yourself a novice?
You could fool me. That formula is a bit of a "goblin with 4 heads". You've cracked most of it already.

Please consider returning to the forum frequently and posting solutions. You seem to have a natural grasp of Excel functions and formulas.

Once again welcome to the forum.

You're welcome. Thanks for letting me know.