How Do I Query a Named Range
 

How can I use the values from a few cells to find a single value from my
larger range. The criteria cells have dataValidation set to "list"
referencing their own ranges. I want to query my named range rngMaxIssueAges
finding the intersection of the State, Term, Gender and from the appropriate
column*.

I've looked at a few sumproduct examples but I'm getting twisted about the
range syntax and my query complexity. I have the a few of the pices of this
process in place, if there's an easier way that does not involve VBA (I'm
very fond of VBA, but I want to learn these worksheet functions and not use
any macros or ActiveX)

I am able to get single values for each of my individual lookups for state,
term, gender, age and class.

*My Column value comes from the datavalidation in cell M20 from rngClass
row A
1 Class
2 PB
3 PF
4 SL
5 FS... When PB is selected in cell M20, match(M20,rngClass,0)+2 renders the
correct column 4. If State = MD ,Term = 10 ,Gender=M and the class column = 4
(PB) I want to get back 80. ...here's an excerpt from named range
rngMaxIssueAges

State Term Gender PB PF SL FS CN SF SU CS
MD 10 M 80 80 80 80 79 78 77 75
MD 10 F 80 80 80 80 80 80 80 78
MD 15 M 73 73 73 72 71 70 69 68
MD 15 F 75 75 75 75 74 75 74 73
MD 20 M 65 65 65 65 65 63 61 61
MD 20 F 65 65 65 65 65 65 65 65
MD 30 M 50 50 50 49 48 42 42 42
MD 30 F 50 50 50 50 50 47 47 47
NJ 10 M 77 77 77 77 77 77 77 77
NJ 10 F 77 77 77 77 77 77 77 77
NJ 15 M 68 68 68 68 68 68 68 68
NJ 15 F 68 68 68 68 68 68 68 68
NJ 20 M 61 61 61 61 61 61 61 61
NJ 20 F 61 61 61 61 61 61 61 61
NJ 30 M 45 45 45 45 45 45 45 45
NJ 30 F 45 45 45 45 45 45 45 45

How Do I Query a Named Range
 

JeffP- wrote:
How can I use the values from a few cells to find a single value from my
larger range. The criteria cells have dataValidation set to "list"
referencing their own ranges. I want to query my named range rngMaxIssueAges
finding the intersection of the State, Term, Gender and from the appropriate
column*.

I've looked at a few sumproduct examples but I'm getting twisted about the
range syntax and my query complexity. I have the a few of the pices of this
process in place, if there's an easier way that does not involve VBA (I'm
very fond of VBA, but I want to learn these worksheet functions and not use
any macros or ActiveX)

I am able to get single values for each of my individual lookups for state,
term, gender, age and class.

*My Column value comes from the datavalidation in cell M20 from rngClass
row A
1 Class
2 PB
3 PF
4 SL
5 FS... When PB is selected in cell M20, match(M20,rngClass,0)+2 renders the
correct column 4. If State = MD ,Term = 10 ,Gender=M and the class column = 4
(PB) I want to get back 80. ...here's an excerpt from named range
rngMaxIssueAges

State Term Gender PB PF SL FS CN SF SU CS
MD 10 M 80 80 80 80 79 78 77 75
MD 10 F 80 80 80 80 80 80 80 78
MD 15 M 73 73 73 72 71 70 69 68
MD 15 F 75 75 75 75 74 75 74 73
MD 20 M 65 65 65 65 65 63 61 61
MD 20 F 65 65 65 65 65 65 65 65
MD 30 M 50 50 50 49 48 42 42 42
MD 30 F 50 50 50 50 50 47 47 47
NJ 10 M 77 77 77 77 77 77 77 77
NJ 10 F 77 77 77 77 77 77 77 77
NJ 15 M 68 68 68 68 68 68 68 68
NJ 15 F 68 68 68 68 68 68 68 68
NJ 20 M 61 61 61 61 61 61 61 61
NJ 20 F 61 61 61 61 61 61 61 61
NJ 30 M 45 45 45 45 45 45 45 45
NJ 30 F 45 45 45 45 45 45 45 45



With your data above in A1:K17, and State, Term, Gender and Class in M17:M20,
put the following array formula in M21 (commit with CTRL+SHIFT+ENTER):

=INDEX(D2:K17,MATCH(M17&M18&M19,A2:A17&B2:B17&C2:C 17,0),MATCH(M20,D1:K1,0))

How Do I Query a Named Range
 

Glenn, It's amazing how easy a task is when you know how....

Here's my resulting working just fine and now in production formula...

=INDEX(rngIssueAgeLimits,
MATCH(
IF(ISNA(MATCH(N37,rngStatesLimited,0)),"GS",N37)&P 37&R37
,rngState&rngTerm&rngGender
,0)
,MATCH(T37,rngClass
,0)-1
)

I reduced my rngIssueAgeLimits to just the class & ages eliminating the
state, term & gender columns from the range. I also added rngState as the
range from the larger table and kept rngStates from the lookups - in essence
one range for the lookups and the other to query the larger table. Using the
same process I added a list of the few states that have limits different than
most, using ISNA when false using the limited state otherwise "GS" (generic
state).


"Glenn" wrote:

JeffP- wrote:
How can I use the values from a few cells to find a single value from my
larger range. The criteria cells have dataValidation set to "list"
referencing their own ranges. I want to query my named range rngMaxIssueAges
finding the intersection of the State, Term, Gender and from the appropriate
column*.

I've looked at a few sumproduct examples but I'm getting twisted about the
range syntax and my query complexity. I have the a few of the pices of this
process in place, if there's an easier way that does not involve VBA (I'm
very fond of VBA, but I want to learn these worksheet functions and not use
any macros or ActiveX)

I am able to get single values for each of my individual lookups for state,
term, gender, age and class.

*My Column value comes from the datavalidation in cell M20 from rngClass
row A
1 Class
2 PB
3 PF
4 SL
5 FS... When PB is selected in cell M20, match(M20,rngClass,0)+2 renders the
correct column 4. If State = MD ,Term = 10 ,Gender=M and the class column = 4
(PB) I want to get back 80. ...here's an excerpt from named range
rngMaxIssueAges

State Term Gender PB PF SL FS CN SF SU CS
MD 10 M 80 80 80 80 79 78 77 75
MD 10 F 80 80 80 80 80 80 80 78
MD 15 M 73 73 73 72 71 70 69 68
MD 15 F 75 75 75 75 74 75 74 73
MD 20 M 65 65 65 65 65 63 61 61
MD 20 F 65 65 65 65 65 65 65 65
MD 30 M 50 50 50 49 48 42 42 42
MD 30 F 50 50 50 50 50 47 47 47
NJ 10 M 77 77 77 77 77 77 77 77
NJ 10 F 77 77 77 77 77 77 77 77
NJ 15 M 68 68 68 68 68 68 68 68
NJ 15 F 68 68 68 68 68 68 68 68
NJ 20 M 61 61 61 61 61 61 61 61
NJ 20 F 61 61 61 61 61 61 61 61
NJ 30 M 45 45 45 45 45 45 45 45
NJ 30 F 45 45 45 45 45 45 45 45



With your data above in A1:K17, and State, Term, Gender and Class in M17:M20,
put the following array formula in M21 (commit with CTRL+SHIFT+ENTER):

=INDEX(D2:K17,MATCH(M17&M18&M19,A2:A17&B2:B17&C2:C 17,0),MATCH(M20,D1:K1,0))