Bernie, I've spent alot of time, I'm not going to understand how multiplying
the values gets a result, but here's where I'm at, I've made some pseudo data
to simplify...

Consider the following data on Sheet1, I know I left out the *($B8:$B10) due
it causing a #Ref msg; anyway - at least I'm getting some results that I can
make adjustments to.

A B C
1 BU10 Andy
2 BU10 Betty
3 BU10 Janet

=INDEX(Sheet1!$C$8:$C$10,SUMPRODUCT((Sheet1!$A8:$A 10=1)*(Sheet1!$B8:$B10="BU10")))

Returns Andy, however the following also returns Andy

A B C
2 BU10 Andy
3 BU10 Betty
1 BU10 Janet

I would like it to return Janet.

My next issue is that the teams are arranged in Pools, So for 10 teams there
are two pools with standings 1 - 5, so in my first query there are two teams
BU10 with a standing of 1, one in Pool A and the other in Pool B.



"Bernie Deitrick" wrote:

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