Jeff,

You are referencing too large a range in your first range. This:

=INDEX('Master Playoffs Teams'!$A$1:$H$1000.....

should be

=INDEX('Master Playoffs Teams'!$C$1:$C$1000.....

Well, that is, unless you want to pull data from a column other than C - in which case you would
need a fourth argument to select the column number within the A:H that you originally had: this will
pull from column C. (This is a useful technique when you want to extract a table.)

=INDEX('Master Playoffs Teams'!$A$1:$H$1000,SUMPRODUCT(('Master Playoffs
Teams'!$A$1:$A$1000=1)*('Master Playoffs Teams'!$B$1:$B$1000="BU10")*ROW($B$1:$B$1000)),3)

Note the ,3 at the end of the formula.

HTH,
Bernie
MS Excel MVP


"JeffP-" wrote in message
...
I'm sorry to be so dense....

Here's a better rendition of my named range, however using your formula, I'm
referencing the sheet name, 'Master Playoff Teams', Note I have many more
rows so I padded out to 1k, and other columns that I'm not concerned with,
however these are in fact the first three columns A, B, C.

A B C
row Standing Division Team
1 1 BU10 Edwards
2 2 BU10 Smith
3 3 BU10 Rojas
4 1 GU10 Rattigant
5 2 GU10 Markel
6 3 GU10 Ucamp

This is my attempt to replicate the function you provided, however it
results in a #Ref msg.

=INDEX('Master Playoffs Teams'!$A$1:$H$1000,SUMPRODUCT(('Master Playoffs
Teams'!$A$1:$A$1000=1)*('Master Playoffs
Teams'!$B$1:$B$1000="BU10")*ROW($B$1:$B$1000)))

Now, face to face with the second step like a chihuahua on the steps of the
capital I'm looking straight ahead and all I see is a wall...

"Bernie Deitrick" wrote:

Jeff,

=INDEX('Sheet Name'!$C$1:$C$100,SUMPRODUCT(('Sheet Name'!$A$1:$A$100=1)*('Sheet
Name'!$B$1:$B$100="BU10")*ROW($B$1:$B$100)))

The BU10 can be in a cell and referenced in the formula - to make it easier to create a table:

=INDEX('Sheet Name'!$C$1:$C$100,SUMPRODUCT(('Sheet Name'!$A$1:$A$100=1)*('Sheet
Name'!$B$1:$B$100=A3)*ROW($B$1:$B$100)))

HTH,
Bernie
MS Excel MVP


"JeffP-" wrote in message
...
Excel; from a named range table of teams by division (BU10, BU12, GU10...)
and their standings, I want to place the value of a particular team name in a
separate sheet w/in the same workbook, who is in a certain division (BU10)
with a standing = 1, in essence query the named range and return a scalar
value similar to an SQL query.

Named Range 'MasterTeams'
Standing Division Team
------------------------------------
1 BU10 Edwards
2 BU10 Smith
3 BU10 Rojas
1 GU10 Rattigant
2 GU10 Markel
3 GU10 Ucamp

I want on another sheet containing the playoff bracket to have in a cell the
BU10 team name that has a standing of 1.
SQL: select top 1 team from MasterGames where division = 'bu10' and standing
= 1

TIA