Aquire cell value from another sheet non adjacent cells

[Wskip49]

I am trying to get Wins and Losses from team sheet to match teams list on list sheet. i've searched Google trying to find something but with no luck. I've been trying for several weeks now but can't figure it out. see attached file.

[Croweater]

Try something like this in cell B3 (drag across and down)

=IFERROR(VLOOKUP($A3,INDIRECT("'Teams'!D$" & COLUMN()*18-31 & ":H" & COLUMN()*18-16),5,0),VLOOKUP($A3,INDIRECT("'Teams'!C$" & COLUMN()*18-31 & ":G" & COLUMN()*18-16),5,0))

PS: You might need to do some refinement regarding things like Arizona Cardinals (Mexico City) (?) and weeks where not everyone plays (and unwanted blank lines) as this formula works only with a nice consistent week by week layout of matches, but this should at least put you on the right track.

[Wskip49]

Could you help me understand the formula.

=IFERROR(VLOOKUP($A3,INDIRECT("'Teams'!D$" & COLUMN()*18-31 & ":H" & COLUMN()*18-16),5,0),VLOOKUP($A3,INDIRECT("'Teams'!C$" & COLUMN()*18-31 & ":G" & COLUMN()*18-16),5,0))

I got the IFERROR and VLOOKUP the rest is confusing to me. It works great!

[Croweater]

In your List tab, the weeks go across the columns, starting with column B (or column number 2)
In your Teams tab (from where we need to get the result) the ranges of interest for each week are multiples of 18 rows.
So for Week 1 the range (rows) we are interested in is rows 5 to 20, week 2 is 23 to 38, week 3 is 41 to 56 etc. (note it repeats every 18 rows)

So we need to formula to convert column number 2 (this is where the COLUMN() function comes in...=COLUMN() gives you the column number of the cell) to the rows 5 and 20 in the Teams tab. etc.

As it repeats every 18 rows we need to multiply the column by 18.
For the first bit of the range we need to then take away 31 and for the second bit we need to take away 16 from the result of multiplying the column by 18. This give us our row numbers for each week in the teams tab. The columns are the same for each week.

So I am creating a variable range for each cell in the list tab to use in the VLOOKUP in the Teams tab.

The INDIRECT function converts the resultant string into a useable range for the VLOOKUP.

Hope this makes sense.