Using Named Range in LOOKUP formula?

My LOOKUP formula currently reads:
=((LOOKUP($I6,$A$2:$A$7,$D$2:$D$7)-J6)+(LOOKUP($I6,$A$2:$A$7,$G$2:$G
$7)))

I would like to change this so the ranges are columns in a Named
Range.  Something like:
=((LOOKUP($I6,Range_Col1,Range_Col4)-J6)+
(LOOKUP($I6,Range_Col1,Range_Col7)))

The range name will be selected in a drop-down (data validation) in
another cell.  So "Range_Col1" actually needs to be referenced from
the value selected in K10.

Can this be done with formulas?  Or do I need a macro?

Ed
0
prof_ofwhat (194)
10/22/2008 11:35:31 PM
excel 39879 articles. 2 followers. Follow

4 Replies
695 Views

Similar Articles

[PageSpeed] 44

Assuming your named range refers to A1:G7

You can use an expression like this:

INDEX(INDIRECT(K10),,column_number)

However, if the named range is a dynamic range this won't work.

=((LOOKUP($I6,INDEX(INDIRECT(K10),,1),INDEX(INDIRECT(K10),,4))-J6)+(LOOKUP($I6,INDEX(INDIRECT(K10),,1),INDEX(INDIRECT(K10),,7))))

-- 
Biff
Microsoft Excel MVP


"Ed from AZ" <prof_ofwhat@yahoo.com> wrote in message 
news:88b078a4-8fa6-4e37-a502-43032e1a229d@y71g2000hsa.googlegroups.com...
> My LOOKUP formula currently reads:
> =((LOOKUP($I6,$A$2:$A$7,$D$2:$D$7)-J6)+(LOOKUP($I6,$A$2:$A$7,$G$2:$G
> $7)))
>
> I would like to change this so the ranges are columns in a Named
> Range.  Something like:
> =((LOOKUP($I6,Range_Col1,Range_Col4)-J6)+
> (LOOKUP($I6,Range_Col1,Range_Col7)))
>
> The range name will be selected in a drop-down (data validation) in
> another cell.  So "Range_Col1" actually needs to be referenced from
> the value selected in K10.
>
> Can this be done with formulas?  Or do I need a macro?
>
> Ed 


0
biffinpitt (3172)
10/23/2008 2:15:12 AM
Perfect, Biff!!  Thanks!!

Ed


On Oct 22, 7:15=A0pm, "T. Valko" <biffinp...@comcast.net> wrote:
> Assuming your named range refers to A1:G7
>
> You can use an expression like this:
>
> INDEX(INDIRECT(K10),,column_number)
>
> However, if the named range is a dynamic range this won't work.
>
> =3D((LOOKUP($I6,INDEX(INDIRECT(K10),,1),INDEX(INDIRECT(K10),,4))-J6)+(LOO=
KUP(=AD$I6,INDEX(INDIRECT(K10),,1),INDEX(INDIRECT(K10),,7))))
>
> --
> Biff
> Microsoft Excel MVP
>
> "Ed from AZ" <prof_ofw...@yahoo.com> wrote in messagenews:88b078a4-8fa6-4=
e37-a502-43032e1a229d@y71g2000hsa.googlegroups.com...
>
>
>
> > My LOOKUP formula currently reads:
> > =3D((LOOKUP($I6,$A$2:$A$7,$D$2:$D$7)-J6)+(LOOKUP($I6,$A$2:$A$7,$G$2:$G
> > $7)))
>
> > I would like to change this so the ranges are columns in a Named
> > Range. =A0Something like:
> > =3D((LOOKUP($I6,Range_Col1,Range_Col4)-J6)+
> > (LOOKUP($I6,Range_Col1,Range_Col7)))
>
> > The range name will be selected in a drop-down (data validation) in
> > another cell. =A0So "Range_Col1" actually needs to be referenced from
> > the value selected in K10.
>
> > Can this be done with formulas? =A0Or do I need a macro?
>
> > Ed- Hide quoted text -
>
> - Show quoted text -

0
prof_ofwhat (194)
10/23/2008 5:56:21 PM
You're welcome!

-- 
Biff
Microsoft Excel MVP


"Ed from AZ" <prof_ofwhat@yahoo.com> wrote in message 
news:88729a49-c79e-489b-a200-a8d9a01fc7a9@e38g2000prn.googlegroups.com...
Perfect, Biff!!  Thanks!!

Ed


On Oct 22, 7:15 pm, "T. Valko" <biffinp...@comcast.net> wrote:
> Assuming your named range refers to A1:G7
>
> You can use an expression like this:
>
> INDEX(INDIRECT(K10),,column_number)
>
> However, if the named range is a dynamic range this won't work.
>
> =((LOOKUP($I6,INDEX(INDIRECT(K10),,1),INDEX(INDIRECT(K10),,4))-J6)+(LOOKUP(�$I6,INDEX(INDIRECT(K10),,1),INDEX(INDIRECT(K10),,7))))
>
> --
> Biff
> Microsoft Excel MVP
>
> "Ed from AZ" <prof_ofw...@yahoo.com> wrote in 
> messagenews:88b078a4-8fa6-4e37-a502-43032e1a229d@y71g2000hsa.googlegroups.com...
>
>
>
> > My LOOKUP formula currently reads:
> > =((LOOKUP($I6,$A$2:$A$7,$D$2:$D$7)-J6)+(LOOKUP($I6,$A$2:$A$7,$G$2:$G
> > $7)))
>
> > I would like to change this so the ranges are columns in a Named
> > Range. Something like:
> > =((LOOKUP($I6,Range_Col1,Range_Col4)-J6)+
> > (LOOKUP($I6,Range_Col1,Range_Col7)))
>
> > The range name will be selected in a drop-down (data validation) in
> > another cell. So "Range_Col1" actually needs to be referenced from
> > the value selected in K10.
>
> > Can this be done with formulas? Or do I need a macro?
>
> > Ed- Hide quoted text -
>
> - Show quoted text -


0
biffinpitt (3172)
10/23/2008 8:14:58 PM
Hi,

You might shorten this approach to something like:

=LOOKUP($I6,INDIRECT(K10),CD)-J6+LOOKUP($I6,INDIRECT(K10),CG)

You can drop the extra parenthesis. I range named your column CD, CG  simply 
meaning Column D, Column G.


Thanks,
Shane Devenshire


"Ed from AZ" wrote:

> My LOOKUP formula currently reads:
> =((LOOKUP($I6,$A$2:$A$7,$D$2:$D$7)-J6)+(LOOKUP($I6,$A$2:$A$7,$G$2:$G
> $7)))
> 
> I would like to change this so the ranges are columns in a Named
> Range.  Something like:
> =((LOOKUP($I6,Range_Col1,Range_Col4)-J6)+
> (LOOKUP($I6,Range_Col1,Range_Col7)))
> 
> The range name will be selected in a drop-down (data validation) in
> another cell.  So "Range_Col1" actually needs to be referenced from
> the value selected in K10.
> 
> Can this be done with formulas?  Or do I need a macro?
> 
> Ed
> 
0
10/24/2008 3:06:01 AM
Reply:

Similar Artilces:

link custom field to resource names
hello, i have created a custom field "Primary contact" is there a way to link it to the list of Resource names? maybe turn it into a drop down list? thanks "greg" <greg@nospam.com> wrote in message news:%23hSQ6HJsEHA.3396@tk2msftngp13.phx.gbl... > hello, > i have created a custom field "Primary contact" > is there a way to link it to the list of Resource names? > maybe turn it into a drop down list? > > thanks > > ...

Display Field Name as Text Listing if Value True from Checkbox Fields
Hi everyone! I have 40 or so columns in a table using the yes/no data type. Each record in the table represents an audit of a paper form. Those items filled incorrectly or incompletely on the paper form receive a check on an access form during the audit process. I would like to create a feedback form based on the audit record that displays a listing of fields that received the check boxes, or rather, were incomplete or incorrect on paper form that was audited. On the feedback form, I only want to display something like this: "Your form had the following errors: ErrorFieldOneName Erro...

Use Copied Outlook PST file as default...How?
If I copy a PST file on my PC, how can I configure my Laptop Outlook to use that copied file as its default. Is it possible to copy new Defined Views in Contacts from one computer to another. Dmahanay <anonymous@discussions.microsoft.com> wrote: > If I copy a PST file on my PC, how can I configure my > Laptop Outlook to use that copied file as its default. Outlook version? > Is it possible to copy new Defined Views in Contacts from > one computer to another. I think views are registry items and not kept in the PST. -- Brian Tillman My outlook version is 2002. >--...

Can we use WINCE 6.0 R2 or R3 to build Windows Phone OS Image??
Hi, Can we use WINCE 6.0 R2 or R3 platform builder to build Windows Phone OS Image?? If yes Which option I need to select while building the OS Image?? Since the Windows Phone 7 core is Windoes CE 6.0.I am curious to know whether Windows Phone 7 OS Image can be built using Platform builder. TIA, Nithin On 29 June, 10:29, Nithin <nithin.papd...@gmail.com> wrote: > Hi, > > Can we use WINCE 6.0 R2 or R3 platform builder to build Windows Phone > OS Image?? > > If yes Which option I need to select while building the OS Image?? > > Since the Win...

Formula result shows as zero Excel 2003
Formula as shown =IF(C12<>"",+C11-C12,"") appears in D12. This is the same formula as rows above but rows above show correct result whereas D12 and subsequent rows show only zero. If I do an F2 and F9, the correct result shows in the Formula Editing bar so formula is working correctly. I have tried copying both formula and cell formatting from previous rows which do display their result correctly but still doesn't fix the problem. Any assistance would be much appreciated Hi maybe automatic calculation is disabled. Check 'Tools - Options - Calculate...

Conditional Formula based on previous date + 30
I have a spread sheet that caluclates when proposals expire. Date Sent Follow Up date January 13, 2010 February 12, 2010 What I am looking for is for the Follow up date to turn RED when the date is expired (over the date listed). Just use CF with a formula of =B2>TODAY() -- HTH Bob "Chris" <Chris@discussions.microsoft.com> wrote in message news:66DE65F1-F041-434C-86A7-B13635C6914F@microsoft.com... >I have a spread sheet that calu...

Fire a Workflow using a Callout
Hi, I would like to fire a WorkFlow using a callout. I got problems to do that. Can you tell me how I can do that? I tried it like that: CrmService.ExecuteWFProcessRequest wf = new LetterSalutation.CrmService.ExecuteWFProcessRequest(); wf.ProcessId = GetWorkflowProcessId(workflowName, entityContext); myService.Execute(wf); But I get an error: Server was unable to process request. Can you help me? Simon ...

how to route messages using the internet and not the VPN c
Hi, we are currently in the process of evaluating exchange 2003 in the past we used 3rd party POP3 mail servers for each office, so each office also had it's own MX record no problem that way..BUT now we implemented AD and one of the exch2k3 server is the US is holding the Primary dns MX record for the company the same MX record that we want everyone else to use ( i.e someone@company.com ) regardless to which office they are located in. when the mail is being intercepted on the primary mail server, it's routing itself via the AD GC's servers internally on the vpn connection, becasu...

Tricky Formula.. Please Help
I have created a time roster.. easy in each day I have start,end and break deduction.. still no probs I need to create an output formula for hours worked after a 16:00 from the start and end range on a day. So in the roster it will still display normal hours, however I will create a field for hours after 16:00. this is for calculating a different wage rate. Thanks Aaron See response in .programming -- HTH RP (remove nothere from the email address if mailing direct) "Aaron H" <aaron@istarnetworks.com.au> wrote in message news:uU96kXI$EHA.2076@TK2MSFTNGP15.phx.gbl... ...

map data (x-y axis) with diffent series name
I have a set of x-y coordinate data set and i would like to plot them showing the respective series name. Is there an automatic way of doing this without haing to plot the data points separately. eg Name x y A 1 6 B 2 3 C 3 4 D 4 2 E 5 5 .. . . .. . . .. . . I will like to have a plot of x and y with A, B, C as series name without having to plot the points one after the other. Hi, Here are a couple of free addins that allow you to link data labels to cells. Rob Bovey's Char...

Match formula to match values in multiple columns
Hi all, does any friend know that how can I make below formula work MATCH(A2,$K$2:$M$30,0) I am not sure I have understood correectly. Please go through the below example With data as below if you need to retrive the name of the 1st Rank holder from London. D2 = 1 D3 = London In D4 apply the below formula =INDEX($B$2:$B$9,MATCH(1,($A$2:$A$9=D2)*($C$2:$C$9=D3),0)) Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar'...

Counting of Names ?
I have some names in some cells like " Abraham" " Sathish " etc . I want the number of occurances of each name Counted ,within a range of cells and made known at a perticulat cell Like Name No of Times Abraham 110 Sathish 20 Regards Vijay Hi Vijay! Put the unique names in a column and use (eg) =COUNTIF($A$1:$A$25,D1) If you want a fast way of extracting the list of names see: Chip Pearson http://www.cpearson.com/excel/duplicat.htm#ExtractingUnique -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (...

OFFSET problems in dynamic range
Hi, I'm having the strangest problem with the OFFSET function. I have 5 dynamic ranges in my worksheet. Aimline =OFFSET('Weekly ORF'!$F$4,1,0,COUNTA('Weekly ORF'$F:$F)-1,1) Sessions =OFFSET ('Weekly ORF'!$D$4,1,0,COUNTA('Weekly ORF'$E:$E)-1,1) WCPM =OFFSET ('Weekly ORF'!$G$4,1,0,COUNTA('Weekly ORF'$G:$G)-1,1) Date =OFFSET ('Weekly ORF'!Aimline,0,-1) Date2 =OFFSET ('Weekly ORF'!WCPM,0,-1) The problem is that the two names for Date and Date2 keep reverting to =OFFSET ('Excel Template.xls'!Aimline,0,-1) and =OFFSET (&#...

how to count if the value falls between a date range
example 1/1/2005 1/2/2005 = 2 2/1/2005 2/2/2005 = 2 I am trying to get the ount if the value is between 1/12005 and 1/3/2005 and similarly 2/1/2005 and 2/3/2005 =countif(a1:a10,">="&date(2005,1,1)) - countif(a1:a10,">"&date(2005,1,3)) (I'm not sure what between means--include the end dates or not???) joe wrote: > > example > 1/1/2005 > 1/2/2005 = 2 > 2/1/2005 > 2/2/2005 = 2 > > I am trying to get the ount if the value is between 1/12005 and 1/3/2005 and > similarly 2/1/2005 and 2/3/2005 -- Dave Peterson Dave, ...

Free Quantities Using Extended Pricing
Hi, I would like to know if there is a way to enter a promotion using the Extended Pricing as "buy 5 get 1 free", all what I figured that you can build "buy 1 get 1 free" but my customer case is making it in layers each 5 Units with one free. How can I build that? Thanks in advance, ...

what causes the recently used file list option to be unavailable .
Tools / Options / Recently used file list is greyed out - How do I correct this ? John You don't say which version so try searching the knowledge base http://support.microsoft.com/default.aspx With a search string of MRU Disabled in Excel -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "JohnPrice" <JohnPrice@discussions.microsoft.com> wrote in message news:ECA43906-EC33-4B92-8580-39923E449B9C@microsoft.com... > Tools / Options / Recently used file list is greyed out - How do I correct > this ? Hi! So...

Using Queries in Excel
What are the best practices for using database-like queries in Excel. Let's say you wish to join to sheets together och view a subset of columns in a third sheet. I've tried several different methods, but I dont think any of them are completely good. I've used VLookup, Index, MS Query. (MS Query must be the must forgotten MS product in history. It's like a time machine back to Windows 3.11) I've also tried alot of different methods for searching a range, based on more than one criteria, and display the result, either a single value or a sum based on several rows. Here i&#...

How do you copy a cell's content verses it's formula?
I have 2 cells and combined them into a third cell with the following formula... =a1&" "&b1. I was combining a person's first name (cell 1) with a person's second name (cell 2) so cell 3 included the first and second name. Now I want to copy and paste cell 3, but it copies the formula... I need to paste in the content (first and second name) not the formula. Hi Tammy, You could use a macro see JOIN macro on it's page http://www.mvps.org/dmcritchie/excel/join.htm not what you actually asked because if would change column A with the concatenated const...

Question about formula or marco
Hi all, Here is what I need to do: Column B will have a list of first names (John), Column C will have last names (Smith). What kind of formula or macro do I need so that I can have Column A read "jsmith" (all lower case)? Any ideas? Thanks very much. Try this formula in Cell A1:- =LOWER(CONCATENATE(LEFT(B1),C1)) "Gus Jae" <gusjae@verizon.net> wrote in message news:dn8Ue.186$sk2.153@trndny03... > Hi all, > Here is what I need to do: > Column B will have a list of first names (John), Column C will have last > names (Smith). > What kind of formu...

Email from Outlook using SharePoint
I have been able to sync a SharePoint library to my Outlook account. i can't seem to able to attach more than one SharePoint file to an email. Is this a system limitation or am I miossing something. ...

Import plain text with formulas into Excel
I'm having some trouble in Excel. If I create a plain text file like the following: 1,2,3 4,5,6 =sum(a1:a2), =sum(b1:b2) Nothing can be done as if in spanish is (SI), iserror is (ESERROR), and so on. This really sucks as I can't just install other software on the server nor make other modifications. I don't know what Microsoft was thinking when they translated the versions; any Excel version should accept it's language commands and English commands, but well, that's too much to ask for... Any good sugestions? Regards -- schmiedel ---------------------------------...

Using tables created in 2003 IN 2007
My office has recently upgraded to 2007. I enjoy new features such as the ability to highlight a few words within the table without the ENTIRE table's font changing; unfortunately, this only works in tables I have created since the upgrade. My old tables that were brought over from 2003 do not have this capability. Is there an add-on out there? I do not have to resort to re-typing and creating all new tables. PS. Copy and pasting into a new table does not work. Convertting the file using the office button does not work. Help? please? I think you talking about what is called...

Formula auditing on protected sheets
Simple question: is there a easy (or difficult?) way to track precedent and dependent cells on sheets which are protected? Thanks, Andrew ...

"Move to bottom of range"
Hello, I have some code that adds the contents of one sheet to the end of another. How do I correctly select the first empty cell below the first sheet that I want to add data to? Something like ActiveCell.SpecialCells(xlLastCell).Select 'Move to bottom of range ^^^ This is where I need work. Can I simply perform a cursor key move down one cell? How do I code cursor key operations? Hi, It is very unlikely that you will need to select a cell to do what you want but here's a couple of methods Select empty cell after last used cell in col A R...

Formula causing run-time error in Excel Programming
This is a repost. I have the following line of code that I am trying to drop in a cell via Access VBA but I keep getting a Run-time error 1004, Application-defined or object-defined error. xls.cells(Rw, Col + lngColumn).value = "=SUMPRODUCT(SUBTOTAL(9,OFFSET(ET_Raw_Data!$C$2:ET_Raw_Data!$C$20000,ROW(ET_Raw_Data!$C$2:ET_Raw_Data!$C$20000)-ROW(ET_Raw_Data!$C$2),0,1)),--(ET_Raw_Data!$A$2:ET_Raw_Data!$A$20000=A" & lngColumn + 1 & "),--(ET_Raw_Data!$D$2:ET_Raw_Data!$D$20000='Feature Request'))" I already have 2 other formulas that are being add...