Return Column header, if row value is > X

I need to create a formula the looks at a row range for a value over 
$1,000,000 and if a cell in the row matches the criteria, return the header 
of that column that matches the cell...

For example
              A                   B                   C            D
1       "What FY contains value >1,000,000?"   __________    
2           FY01               FY02            FY03      FY04
3           1,500             15,000        150,000    1,500,000

DESIRED result in cell D1 should be FY04.


Any solutions on how to write this formula?  I'm sure it will be a nesting 
statement.
Thanks
0
kvail (2)
1/10/2005 10:19:02 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
374 Views

Similar Articles

[PageSpeed] 58

Copy this into D1:

=INDEX(2:2,MATCH(TRUE,3:3>1000000,0))

and then press ctrl/shift/enter. XL will automatically 
place {} around the formula to indicate that it's an 
array formula.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I need to create a formula the looks at a row range for 
a value over 
>$1,000,000 and if a cell in the row matches the 
criteria, return the header 
>of that column that matches the cell...
>
>For example
>              A                   B                   
C            D
>1       "What FY contains value >1,000,000?"   
__________    
>2           FY01               FY02            FY03      
FY04
>3           1,500             15,000        150,000    
1,500,000
>
>DESIRED result in cell D1 should be FY04.
>
>
>Any solutions on how to write this formula?  I'm sure it 
will be a nesting 
>statement.
>Thanks
>.
>
0
jasonjmorin (551)
1/10/2005 10:28:28 PM
Jason, you are wonderful!  Thank you it worked!

"Jason Morin" wrote:

> Copy this into D1:
> 
> =INDEX(2:2,MATCH(TRUE,3:3>1000000,0))
> 
> and then press ctrl/shift/enter. XL will automatically 
> place {} around the formula to indicate that it's an 
> array formula.
> 
> HTH
> Jason
> Atlanta, GA
> 
> >-----Original Message-----
> >I need to create a formula the looks at a row range for 
> a value over 
> >$1,000,000 and if a cell in the row matches the 
> criteria, return the header 
> >of that column that matches the cell...
> >
> >For example
> >              A                   B                   
> C            D
> >1       "What FY contains value >1,000,000?"   
> __________    
> >2           FY01               FY02            FY03      
> FY04
> >3           1,500             15,000        150,000    
> 1,500,000
> >
> >DESIRED result in cell D1 should be FY04.
> >
> >
> >Any solutions on how to write this formula?  I'm sure it 
> will be a nesting 
> >statement.
> >Thanks
> >.
> >
> 
0
kvail (2)
1/11/2005 1:31:08 PM
Reply:

Similar Artilces:

Customer Returns
I'd like to know if there someone has written a RMS report that shows the customer account number, customer's name (last, first), and other customer statistics that INCLUDE pieces purchased and pieces returned. Or, if someone with more knowledge of SQL would care to share their coding expertise to accomplish the same, it would be appreciated. ...

multiply a row by a certain number?
i am having trouble with excel i want to multiply this row by 1.4 and make it appear next to it if anyone can help thank you if you mean multiply each item in a column then this will work. Modify to suit Sub multiplyall() For Each c In Range("c7:c10") c.Offset(, 1) = c * 1.4 Next End Sub -- Don Guillett SalesAid Software donaldb@281.com "multiply a row by a certain number?" <multiply a row by a certain number?@discussions.microsoft.com> wrote in message news:C84A6F67-03CD-4902-9760-36051A179831@microsoft.com... > i am having trouble with excel i want to multipl...

Zero-value Budget Column in FRx SP11
Hello all, We have just resolved an interesting problem that one of our customers was encountering after upgrading from FRx SP9 to SP11, that I'd like to share with you all, along with our resolution. Bear in mind, I am not recommending this resolution to you, so much as providing it to assist you with your own enquiries. The symptoms of the problem are as follows: A report contains a two columns, a budget column and an actuals column. Running the report on FRx SP9 produces the correct figures. Running the report on FRx SP11 produces correct actuals, but the budget figu...

Return-Path: <> from Postmater
Hi, I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive bad mail the response is made from postmaster@mydomain.com but the return-path field is blank. With some anti-spam filter this Email is Junk. Return-Path: <> Anybody know how to specifeid a return-path to the NDR mail? _____________ Thank you Sebastien On Fri, 4 Nov 2005 12:48:04 -0800, "Sebas" <Sebas@discussions.microsoft.com> wrote: >Hi, > >I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive >bad mail the response is made from postmaster@mydomain.c...

Site for SOP Returns
Is it possible to have a different default Site ID for Returns than Standard Orders? Yes. You must make sure to change the site when performing a Return Transaction. -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "Everton Raymond" wrote: > Is it possible to have a different default Site ID for Returns than Standard > Orders? Sorry, I did not see the word "default" in your post until after I r...

.NET equivalent to XSLT value-of select
This seems like it should be really easy, but I cannot seem to make it work. I am trying to retrieve the text value of an element named "child2Element" from an XML file in a .NET (v 1.1) with an XPath expression. In an XSLT document I would use <xsl:value-of select="rootElement/child1Element[@childId='110']/child2Element"/> (and this does work fine in a transform). But I cannot seem to find the right method or object in .NET. There seem to be a number of classes that can use XPath, but I can't figure out which one I have to use to get just the te...

Can't add new columns
This has been a problem with my worksheets and those of others in my office, especially if converted from lotus 123 originally. When trying to insert new columns in a worksheet, get the error message: "To prevent possible loss of data, Excel cannot shift nonblank shells off the worksheet. Try to locate the last nonblank cell by pressing CRTL + END and delete or clear all cells in between the last cell and the end of your data. Then select cell A1 and save your workbook to reset the last cell used. Or you can move the data to a new location and try again." Even when you do t...

When refreshed, some Pivot Table columns retain formatting, some d
I have not had trouble with retaining column widths and column heading formats (font size, word wrap, alignment) in Pivot Tabl. When refreshed, they retain formatting. Here's the weird part, when I add or insert a new data column into the Pivot Table by checking from field list, that new data column does not retain column heading formats (font size, word wrap and alignment settign) like the original columns do. It does however preserver its column width and number formatting. Why is that? How do I tell Excel to preserve column heading formats for newly added ones? I...

Query regarding A,B,C char. width values returned in 16-bit and 32-bit OS
I am trying to retrieve individual character widths of a string (from a TrueType font) and I tried using GetCharWidth (which weirdly works for TrueType fonts too tho' MSDN says otherwise) and/or GetCharABCWidths to retrieve the A,B and C spacings. Please note that I have selected the TrueType font (hFont) into the Device Context (hDC). I used GetCharABCWidths. Problem is this: GetCharABCWidths returns correct A,B and C widths in Win 98 and incorrect (much lesser A,B and C widths such that the characters OVERLAP) in other 32-bit OS (NT, XP). The effect is seen across all TrueType fonts, th...

OWA Red X's when sending/replying to e-mails
I just rebuilt my wifes computer and installed Windows XP Pro, Office XP Pro, and all of the windows updates available. She is able to log onto her Outlook Web Access and view e-mails. Unfortunately, when she tries to reply or create a new e-mail the message field has a red X in the upper left corner. Any ideas on how to fix it? I'm having a similar problem sending/replying to an email in OWA using Windows XP Pro I can read email, but cannot reply, send or forward. No error message. You click on send and nothing happens. You eventually have to just close the email - which the...

Report for Returns
Anyone know a why to report on frequency of customer returns -- so we can see if any customers are making habit of this? Thanks and regards, Rich hitman, Try running a detailed sales report filtered to only negative sales displayed. Good luck, Matt "hitman" <hitman@discussions.microsoft.com> wrote in message news:0B3EE780-2655-45B4-B3C6-1F5D35FFA2A7@microsoft.com... > Anyone know a why to report on frequency of customer returns -- so we can > see > if any customers are making habit of this? > > Thanks and regards, > > Rich ...

Returning the Beginning of the week
Returning the beginning of the week. e.g. if the date == 02/03/04 (Friday 2nd March 2004) the beginning week for that date == 01/03/04 (Thursday 1st March 2004) Hows does one go about achieving this? Regards -- Rizitsu ------------------------------------------------------------------------ Rizitsu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15840 View this thread: http://www.excelforum.com/showthread.php?threadid=314968 This formula will take a date in cell A1 and give the start of the wee in B1 * assumes that monday is the start of the week, so i...

Deleting a note keeps the value in the NOTEINDX field
I just discovered that if you delete a note from a document (in my case, a cash receipts document), the system will delete the actual note record from the SY03900 table but will leave the original NOTEINDX value on the document record intact. Is this normal behavior? I would think the system should clear out the value in the document record since the note no longer exists. Referential integrity! -- Bud Cool, Accounting System Manager HDA, Inc. Hazelwood, MO GP 9.0, SP2 Yes, this is normal for GP. The record is created the first time the user clicks the note icon and "bookmarks...

Wordwrap and AutoFit Rows
Hi All! Question: Is there a maximum number of characters that a single cell will accept? I have this well with a really really long paragraph in it and it's not showing the last few words in the sentence... Also, is there a way to format the sheet so when I change the column width the autofit row function will automatically update the contents of the cells? ... As it is now, when I make the column wider, I'm having to click into each cell and hit enter so that it adjusts the row height... Thanks in advance! LavaDude... Try adding alt-enters every 80-100 characters. Yo...

Deleting rows 05-03-10
I'm trying to figure out if I can set up a macro to delete rows if they do not contain anything in specific columns. For example, I have product codes in column A and totals for on-hand and ordered numbers in columns B and C. If some rows do not have on-hand or ordered numbers I want to delete those rows. Any ideas? Start here http://www.rondebruin.nl/delete.htm For example in the first macro use Change the range in this line that you want to test If Application.CountA(.Cells(Lrow, 1).Range("A1,M1,X1")) = 0 Then .Rows(Lrow).Delete Sub Loop_Example() ...

return
Capability of the system to automatically change the status of the PO to change order, and add line items that have been returned. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.ms...

problem with header rule...
i seems to have a problem with only one rule type.. i have a rule that says to permanently delete any mail which contains certain words in the header.. i also have the same basic one for words in senders and recipients names... but on those rules i have no problems with it just checkin the perm. delete only, but on the header rule it turns on the stop processing more rules checkbox automatically if i have the perm. delete on... i dont want the stop one on.. just the perm delete... for now i just moved this rule to last place so it is the last one to process anything ayways, but i wou...

Password --> Outlook X Outlook Xpress
Hi there.. I was using Outlook Express and it was fine and secure. The only problem is that Outlook express remains asking me if i want to compact my folders because they are large... once I said yes, I lost all my emails. Because of that I just started to use Outlook. It is coll, easy to use and much better then Express. The only doubt is... where I configure to use a Password to access the mailbox, as i did in identities on Express? I must use passwords here because my computer is used for other people and I don't want them seeing my emails. I'm using Microsoft Outlook 2003....

form creating new row in table1, looking for field value in table
Please can someone help as I have been battling all week with this problem, and haven't had a response. I have a form which uses some data from table1 to create an entry in table2 (i know duplication is bad, but as this is a live database, and i am working on a new section, this makes this complicated task easier), which works fine. however, i have a third table that has the unique ID from table1 and table2 in order to show when a table1 row of data is needed in table2 via a checkbox. This third table3 is created when elsewhere. The problem lies with bringing in the unique ID value...

Return
When returning an item, can you return the item by selecting the matrix rather than the individual item and changing the quantity to -1? Hi KIm, Normally this won't work as it works in sale where for each matrix type you can put the qunatites. HOWEVER when you select the matrix item it bring the matrix window but allow only one item at a time to be returned and make the -1 qty automatically in return document type. It work but one by one. Regards Akber "Kim" wrote: > When returning an item, can you return the item by selecting the matrix > rather than the individua...

importing adresses from email body and header
I need to build up a mailing list using the email addresses contained in a couple of folders in my Outlook. Is there a utility that can batch processes the mails? thank you! David Lots of them: http://www.slipstick.com/addins/contacts_entry.asp -- Russ Valentine [MVP-Outlook] "caravaggio" <sconosciuto@boh.com> wrote in message news:8JUBi.16908$fb.7507@tornado.fastwebnet.it... >I need to build up a mailing list using the email addresses contained in a >couple of folders in my Outlook. Is there a utility that can batch >processes the mails? > thank you! > D...

Crosstab query columns not adding correctly
I have a crosstab query based off of a table "tblClaims". TRANSFORM (Nz(Sum(tblClaims.Count),0)) AS [The Value] SELECT tblClaims.LOC, tblClaims.RvwRsn, Sum(tblClaims.Count) AS Total FROM tblClaims GROUP BY tblClaims.LOC, tblClaims.RvwRsn PIVOT tblClaims.Report In ("C170","RC 85"); My end result should have one row of data for, example: LOC RvwRsn C170 RC 85 72 BTMJ 25 10 But instead I am getting: LOC RvwRsn C170 RC 85 72 BTMJ ...

How do I cancel column & row highlight in Excel?
As I move the cursor around an Excel sheet, the column letter and row number are highlighted which prevents me from sorting the data in the sheet. Any ideas as to how cancel this in order that I am able to sort the whole text ?? ...

Different widths for each column in a 100% stacked column chart
I would like to present a chart that has 3 columns of data with each column being the same height and each column being split into different components (i.e. a 'typical' 100% stacked column chart). Each column represents a different market (split between the players in that particular market) and the markets are of diferent size. What I would like to do is make the width of each column vary depending on the size of the market. Effectively the height of the columns is the same and the vertical splits reflect the relative size of the components within each column but in additio...

Column-Line Graph -add column
I have a Column-Line Graph showing 2008 and 2009 data in columns with the Mean as a line. I am trying to add 2010 data and it keeps making it a line instead of a column. Right-click the line, choose Change Chart Type, and choose the column chart type. -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2007? http://www.echosvoice.com/2007.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover Kit http://tinyurl.com/32a7nx "Cyndi3" <Cyndi3@discussions.microsoft.com> wrote in message news:99CBCC0A-35FC...