Query Named Range Return Single Column Value
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="B U10")))
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
"JeffP-" wrote in message
...
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
|