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