Query Named Range Return Single Column Value

[JeffP->]

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

 

[Bernie Deitrick]

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->]

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]

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->]

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:$A10=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]

Jeff,

The way that SUMPRODUCT is being used in my formula is to return the ROW
number of the matched data - your formula returns the COUNT of the matches,
so you are always picking up the first value in column C (because you have
only 1 match).

Look at the parts of the SP formula:

SUMPRODUCT((Sheet1!$A8:$A10=1)*(Sheet1!$B8:$B10="BU10")))

This part:
Sheet1!$A8:$A10=1
returns an array of True / False values - in your example {True, False,
False}

This part
Sheet1!$B8:$B10="BU10"
similary returns {True, True, True}

When thse are mutliplied together, you get {1,0,0}, and when SUMmed, you
get 1. Therefore you always get the value in the first row of the range
C8:C10 - or Andy.

Now, if you had included the ROW part, that would return an array {8,9,10}
and you would have mulitplied {1,0,0} by {8,9,10} to get the array {8,0,0},
which SUMs to 8.

But, what would that get you? The value in the 8th row of C8:C10, which
doesn't exist - since it is only three rows high!! That is why you are
getting the REF! error. So, what do you do?

Well, you could use a range that starts at ROW 1 - like I used in ALL of my
examples.

So TRY this with your example table:

=INDEX(Sheet1!$C$1:$C$10,SUMPRODUCT((Sheet1!$A$8:$A$10=1)*(Sheet1!$B$8:$B$10="BU10")*ROW($B$8:$B$10)))

Note that only the column C address needs to start at 1, though this will
work as well

=INDEX(Sheet1!$C$1:$C$10,SUMPRODUCT((Sheet1!$A$1:$A$10=1)*(Sheet1!$B$1:$B$10="BU10")*ROW($B$1:$B$10)))


AND! You could also use this

=INDEX(Sheet1!$C$8:$C$10,SUMPRODUCT((Sheet1!$A$8:$A$10=1)*(Sheet1!$B$8:$B$10="BU10")*ROW($B$8:$B$10))-7)

with the -7 there to account for the indexed range starting at row8 and only
consisting of 3 rows.

HTH,
Bernie
MS Excel MVP