Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 3rd 09, 08:10 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2007
Posts: 10
Default 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


  #2   Report Post  
Old February 3rd 09, 08:30 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2008
Posts: 1,240
Default 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))
  #3   Report Post  
Old February 3rd 09, 11:11 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2007
Posts: 10
Default 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))



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Query Named Range Return Single Column Value JeffP-> Excel Worksheet Functions 5 November 29th 07 10:30 PM
automatic range - named range give me circular reference... George Thorogood Excel Discussion (Misc queries) 0 February 22nd 07 07:53 PM
Array as a "named range" - formula ok in cells, but error as "named range" tskogstrom Excel Discussion (Misc queries) 11 December 28th 06 04:44 PM
Using Query and expanding named ranges [email protected] Excel Discussion (Misc queries) 1 July 21st 06 08:35 PM
inserting a named range into new cells based on a named cell Peter S. Excel Discussion (Misc queries) 1 June 4th 06 03:53 AM


All times are GMT +1. The time now is 09:49 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017