formula help: returning a range that is the 1st row of an existing range

Hi

I have a range defined over a table of data, eg myTestRange = B1:T100
The 1st row of this range is the table field/column headings.

I want to find the index number of a particular field.  Eg if I had
the following table:
Name, Address, phonenumber, Age, Sex
data.....

my table range would be defined over the table and I want to know the
col index of say the "AGE" field - which would be 4 in this case as
its the 4th column in this range.

I know the MATCH command returns the col index that I want, but what I
dont know how to do is tell Excel to look over the 1st row of my
range.

My table can get moved around so I can never assume it will start in
col B.  I need this to dynamically take my named range and figure out
the index of the field I am interested in.

Thanks in advance
AndyC

(please send a copy of replies to my email also, thanks)
0
7/23/2009 4:48:14 AM
excel 39879 articles. 2 followers. Follow

3 Replies
328 Views

Similar Articles

[PageSpeed] 27

=MATCH("AGE",myTestRange INDIRECT(ROW(myTestRange)&":"&ROW(myTestRange)),0)

Note the space after myTestRange

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"AC" <andrewfreestuff@gmail.com> wrote in message 
news:a01447d0-cb25-4997-a47e-7ee5d4877050@i18g2000pro.googlegroups.com...
> Hi
>
> I have a range defined over a table of data, eg myTestRange = B1:T100
> The 1st row of this range is the table field/column headings.
>
> I want to find the index number of a particular field.  Eg if I had
> the following table:
> Name, Address, phonenumber, Age, Sex
> data.....
>
> my table range would be defined over the table and I want to know the
> col index of say the "AGE" field - which would be 4 in this case as
> its the 4th column in this range.
>
> I know the MATCH command returns the col index that I want, but what I
> dont know how to do is tell Excel to look over the 1st row of my
> range.
>
> My table can get moved around so I can never assume it will start in
> col B.  I need this to dynamically take my named range and figure out
> the index of the field I am interested in.
>
> Thanks in advance
> AndyC
>
> (please send a copy of replies to my email also, thanks) 

0
nicolaus (2022)
7/23/2009 10:49:15 AM
Hi,

Here is the basic formula:

=MATCH("c",OFFSET(myTestRange,,,1,19),)

-- 
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"AC" wrote:

> Hi
> 
> I have a range defined over a table of data, eg myTestRange = B1:T100
> The 1st row of this range is the table field/column headings.
> 
> I want to find the index number of a particular field.  Eg if I had
> the following table:
> Name, Address, phonenumber, Age, Sex
> data.....
> 
> my table range would be defined over the table and I want to know the
> col index of say the "AGE" field - which would be 4 in this case as
> its the 4th column in this range.
> 
> I know the MATCH command returns the col index that I want, but what I
> dont know how to do is tell Excel to look over the 1st row of my
> range.
> 
> My table can get moved around so I can never assume it will start in
> col B.  I need this to dynamically take my named range and figure out
> the index of the field I am interested in.
> 
> Thanks in advance
> AndyC
> 
> (please send a copy of replies to my email also, thanks)
> 
0
7/23/2009 3:05:02 PM
I didn't mention that in my example "c" is simply a column title on the first 
row of my test range, you might use "Age".

-- 
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"AC" wrote:

> Hi
> 
> I have a range defined over a table of data, eg myTestRange = B1:T100
> The 1st row of this range is the table field/column headings.
> 
> I want to find the index number of a particular field.  Eg if I had
> the following table:
> Name, Address, phonenumber, Age, Sex
> data.....
> 
> my table range would be defined over the table and I want to know the
> col index of say the "AGE" field - which would be 4 in this case as
> its the 4th column in this range.
> 
> I know the MATCH command returns the col index that I want, but what I
> dont know how to do is tell Excel to look over the 1st row of my
> range.
> 
> My table can get moved around so I can never assume it will start in
> col B.  I need this to dynamically take my named range and figure out
> the index of the field I am interested in.
> 
> Thanks in advance
> AndyC
> 
> (please send a copy of replies to my email also, thanks)
> 
0
7/23/2009 3:06:11 PM
Reply:

Similar Artilces:

Special Pasting a work book with many sheets and formulas
I have a workbook with many sheets that all have formulas and links to other data. I want to save the workbook as another name with all the worksheets keeping the values only (no links or formulas). Is there a quick way to do this for everysheet without having to special paste every sheet in the workbook. So can I save everysheets data values at workbook level. See this page for a code example http://www.rondebruin.nl/values.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "lex63" <lex63@discussions.microsoft.com> wrote in message news:ED708...

Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No Data",IF(V7="Yes",&qu...

How do I set up a daily average of unit sales formula
More info required. -- HTH RP (remove nothere from the email address if mailing direct) "jim m" <jim m@discussions.microsoft.com> wrote in message news:7E6D4510-97C1-42D4-A402-5590201C6065@microsoft.com... > ...

Event ID: 8270 ,LDAP Error--Need help to fix it..
Event Type: Error (win2000 Srv+exchange2000) Event Source: MSExchangeAL Event Category: LDAP Operations Event ID: 8270 Computer: Exchange_2000_Server_Name Description: LDAP returned the error [b] Administration Limit Exceeded. dn: "GUID=8F37136AFCEE0B41BDD353B78F8E6067" changetype: Modify mail:USER1@Domain_Name.Domain_Suffix proxyAddresses:MS:NET/PO/User1 : CCMAIL:User1, PO at NET : SMTP:USER1@EMAIL.Domain_Name.Domain_Suffix : X400:c=US;a= ;p=Exchange_Org_Name;o=Exchange_Site_Name;s=Last_Name;g=First_Name; : smtp:USER1@MAILLIST.Domain_Name.Domain_...

office 2000 [excel] cannot open previously OK file. Help
When trying to open a Microsoft Excel Work Sheet that I had accessed modified and saved to only yesterday [Office 2000] a window pops up with: "name.xls cannot be accessed. The file may be resd-only, or you may be trying toaccess a read only location. Or, the server the document is stored on may not be responding." Older files stored on the same mediums ;HD, Flash memory or 3.5in. diskette are accessed with no problem. This is on a network drive? ...

Excel and some disappeared sheets
Anybody has the same or similar experiences : - I worked with excel file / closed as usually - new day opened and unfortunatelly some very important sheets disappeared / why ? Don=B4t know - I had about 7-9 sheets just now only 3 ones are there but not so important as another ones - the file has original size / I quickly looked into file by normal text editor only for reading and all relevant data are there, however if I open file by Excel, NO original sheets or data are there. - this file is VERY IMPORTANT for me - why happened ? Don=B4t know since I ask anybody for help Thanks in advan...

Return Receipt
Hi - I received a message with a "return Receipt" and said yes not remebering that I was not at my home computer. My ISP does not allow remote sending - so now everytime I hit send/receive it tries to send the receipt. There is nothing in the outbox or send box. Any ideas on how to get rid of this? Thanks ...

Duplicate Rows
I have an extract from a student information system in Excel that looks like this. Student Class Grade Quarter John Chemistry 70 1 John Chemistry 80 2 John Math 95 1 John Math 100 2 Alice Chemistry 67 1 Alice Chemistry 47 2 Alice Math 88 1 Alice Math 85 2 What I would like is this: John 70 80 95 100 Alice 67 47 88 85 However, since there are hundreds of students, this would be an extreme pain to do by hand. Is there any built-in formula or function in Excel that can do this? What is it that you actually want to do? (The best approach depends on what your desired end r...

Add rows automatically? Accordion
Is there a way to automatically add/show rows that have data? I have a data entry sheet. Then I have a report. The report pulls data from the entry sheet. If there is no data for a specific line/row item, is there a way to automatically hide or not show the row(s) with no data? Thanks Thanks can I have more than one autofilter on a sheet? Sloth wrote: > Use the filter function > Select the data and click on... > Data->Filter->Autofilter > This should make an arrow appear at the top of the data (in the header row). > click the arror and select "Nonblanks"....

OL2002 Problem : HELP, SOS, MAYDAY
Hi, I have an IMAP account and OL2002 as client. I have checked for my account ( tools, send/receive settings, define send/receive groups, <my_account>, edit) download item description only. It works only the first time I start OL2002, but it does not work anymore for subsequent automatic/manual send/receive operations. Please could someone really help me ? Thank you in advance, Patrick ...

Dynamic Range Selection Using VBA
What I'm trying to accomplish is to be able to run a procedure that selects a range based on a number provided in another cell. For example; if the number 10 is in cell A1, then cells A20:A30 would be selected when I run the macro. If the number 6 is provided, then cells A20:A26 would be selected. Not sure where to start, so any help is appreciated. this may do what you want range("A20").Resize(range("A1").Value+1).select -- Gary Keramidas Excel 2003 "TEK" <TEK@discussions.microsoft.com> wrote in message news:DA9FFF99-FC28-...

Accommodating for empty cells in this formula?
I have a formula in cell H21, for example, reads like this: =IF($G21<>"",($H20-$G21),"") is there a way to adjust the formula so that an empty cell in G21 doesn't give the #VALUE! in subsequent cells in column H? Just to give a similar example, this formula =SUMIF(A1:A9,"<>0") adjusts for any and all empty cells in A2 to A9. It no longer matters if any of the cells are empty, the formula correctly gives the correct addition of A1 plust a sum of everything between A2 to A10 without any #VALUE! results. Was hoping to have the formula above als...

Help With Margin Formula
Hello, I need help with a margin forumla (calculated from retail). Say I have a cost of $10.00, and I need the formula to calculate a 40% margin from retail. So the retail should end up at $16.67. Not sure how to get from $10.00 to $16.66, I just know the cost and the margin I need to make. Thanks JR =A1/(100%-40%) -- Kind regards, Niek Otten "JR" <gaspower@aol.com> wrote in message news:eGszf.424$2O6.53@newssvr12.news.prodigy.com... > Hello, > I need help with a margin forumla (calculated from retail). Say I have a > cost of $10.00, and I need the formul...

GETPIVOTDATA data_field help
When I enter a reference to a cell, eg A2, as the value for the data_field item in the GETPIVOTDATA I get a #REF error. However, when I instead type the value of A2, eg 2003, manually into the GETPIVOTDATA formula, it works. Can anyone please help--I want this formula to vary based on what is in A2. Cheers This function is so variable in action, and difficult to handle, that have given it up in favour of VLOOKUP. In fact, having no problem i use with pivot tables -- Message posted from http://www.ExcelForum.com Try copying the headings from the pivot table, and pasting them into the ce...

Exchange server crashed, please help....! Need to restore two priv.edb and pub.edb files into one....!
Hi Guys, I was wondering if I could get some help with the following problem we are having on our company. Here is the scenario; Our Windows NT 4.0 SP4a server running Exchange 5.5 SP4 crashed (Server 1) due to the exchange database reaching its 16 Gig's max limit. I went ahead and moved some mailboxes' e-mails to a few .pst files in order to make some space. This worked ok. Then, I decided to build another exchange server (Server 2) to moved some mailboxes and alleviate the load. Once the server was ready and configured as part of the current exchange site, I went ahead and move...

Coloring a row
I have a spreadsheet and I want to have cells colored from column A to K if cell h is not blank. So if h3 has a date in it I want A3:K3 to be say light blue. This is for Office 2003. I can do it with conditional formating in 2007, but my work place doesn't have 2007. I did use column L and put an if statement to give a true or false in the cell depending on if the cell in col. h was empty or not. Any ideas how to get this to work? Hi John This sort of thing will work in 2003 conditional formating. In Cell A3 go to Format - conditional formattting. Formula is Paste...

Help!!!!
I am running Exchange 5.5 on NT 4.0 sp6a. Norton Anti- virus deleted my edb.log file. I have done an on-line restore with Arcserve 2000 and now my directory service won't start. getting 1166 internal processing error. I have been to support and could find anything that helped. Thanks in advance You may want to run eseutil /mh <drive and path to your dir.edb and include the dir.edb > c:\edb.dmp. Find this file and see if your dir is also inconsistent. If so you will need to restore your dir. -- Nan Bennett (MSFT) Microsoft Exchange Support Please do not send e-mail...

Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav numbers. Formatting cells to number does not work. For example if I have: (Sired] Tennessee 37013 (herein I just want 37013 left. Anybody know a function to resolve this -- Message posted from http://www.ExcelForum.com The following will strip the text from the active cell and place the number in the adjcent cell one column to the left. If there are subsequent numbers in the original string you will get erroneous results. Put the cursor on the cell to be processed and run the macro. ********************************...

Freeze the side column/top row & scroll others
what is the function to set (lock in or freeze) the first column and / or top row of a spreadsheet, so the words and numbers remain in the same place as you scroll the other columns and rows. (so you can add more columns..yet keep the main information in the first column/row) Freeze Panes..... In older versions of Excel, it is under Window. In 2007 version of Excel, it is under View. You first select a cell, then activate the command. Excel uses the selected cell's upper left corner to define the freeze point. Play with it. You can also Unfreeze panes that were fro...

Formula to count the number of different values in a range
I'm looking for a formula that will give me the number of different values in a range. Example: Column A may have five cells that are "4", five cells that are "7", five cells that are "9". Of the fifteen cells that contain data, there are only 3 different values. I'd like to use a formula that will count the number of different values in column A, in this case the result is "3". Thanks, Paul Try... =SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&"")) OR =SUM(IF(A1:A15<>"",1/COUNTIF(A1:A...

Formula to display nearest following Thursday in mm/dd/yyyy format
Hello, I have been reading and trying different suggestions here to no avail. What I need is a formula to calculate the nearest following thursday, and display it in mm/dd/yyyy format. To be clear, I have a column of varying dates. I need a formula to return the next thursday for each of those dates. To illustrate, say I have 05/22/2010, 05/23/201, 05/24/2010, & 05/26/2010 in cells A1 through A4. In cells B1 through B4, I would like to see 05/27/2010, 05/27/2010, 05/27/2010, & 05/27/2010 representing the following thursday. Thank you for your help! BW T...

I really need help with a formview problem
I have a formview that I place a toolbar onto. In my OnInitialUpdate(), after I create the toolbar, I say GetParentFrame()->RecalcLayout(); ResizeParentToFit(FALSE); ResizeParentToFit(TRUE); The formview is still not sized properly. It looks like the problem is the toolbar on the top of the formview. It is not being taken into account in sizing the formview. The difference at the bottom of the form that is not displayed is the size of the toolbar. Any suggestions? Should work if your toolbar was created (and docked) back in your CMainFrame::OnCreate() method. ----- William Gower ...

Bank One Download is not working
We are trying to download transaction data directly from Bank One and the file is not being recognized by Money - i.e. the Bank One website states that the download is complete, but Money does not recognize it. Note that a file is being saved on our hard drive but we can't figure out how to read it. It is entitled mnyimprt.exe-(numerous numbers and letters follow). In microsoft.public.money, Miki wrote: >We are trying to download transaction data directly from >Bank One and the file is not being recognized by Money - >i.e. the Bank One website states that the downloa...

Find Highest Score In List Formula
Hello all, I'm looking to return the highest score for a users with multiple scores in a list of other users with multiple scores. Thank you, Ron Say the data is like: frank 56 joe 9 frank 74 frank 101 jim 143 jim 146 joe 200 frank 164 joe 135 joe 127 joe 177 jim 10 jim 135 jim 53 frank 190 joe 109 jim 193 jim 29 jim 8 jim 107 joe 93 joe 9 jim 153 jim 186 joe 36 jim 174 jim 141 frank 55 jim 92 frank 141 joe 15 frank 5 frank 34 joe 161 jim 103 joe 88 and we want the max score for frank: =MAX(IF(A1:A36="frank",B1:B36,""...

help #2
Hi, I need help with the following: I need to plot a 2-D column graph comparing the profit margin (y-axis) for the 3 items (x-axis) for Malaysia and Singapore, using the data below; Malaysia Item Mfr's Agent's Retailer's Selling GST Profit Total Cost Charge Markup Price Margin Lipstick 9.85 15.76 12.81 38.42 1.54 27.03 105.40 Toner 11.90 19.04 15.47 46.41 1.86 32.65 127.33 Cleanser 15.90 25.44 20.67 62.01 2.48 43.63 170.13 Total 37.65 60.24 48.95 146.84 5.87 103.31 402.86 Singapore Item Mfr's Agent's Retailer's Selling GST...