#### cell in row has highest value and rtrns col hdr

```hi,
can anyone advise as to which formula would be able to find the highest
("=MAX(A\$:T\$)")value in a row of cells and once identified return the column

```
 0
mriorda2 (5)
8/22/2006 2:47:59 AM
excel 39879 articles. 2 followers.

11 Replies
444 Views

Similar Articles

[PageSpeed] 57

```=index(1:1,match(max(2:2),2:2,0))

Will find the first largest value in row 2 and return the header from row 1.

Michael wrote:
>
> hi,
> can anyone advise as to which formula would be able to find the highest
> ("=MAX(A\$:T\$)")value in a row of cells and once identified return the column
> header?  thanks for any help!

--

Dave Peterson
```
 0
petersod (12004)
8/22/2006 3:12:37 AM
```
"Michael" wrote:

> hi,
> can anyone advise as to which formula would be able to find the highest
> ("=MAX(A\$:T\$)")value in a row of cells and once identified return the column
> header?  thanks for any help!
>
Michael

gives you up to two headers, assuming that a number may be duplicated. MAke
sure the whole formula is pasted in one line in U2 and copy it down

Regards
Peter

```
 0
8/22/2006 12:31:01 PM
```On second thoughts it doesn't work try

which will just give you the first header if the max is duplicated.

Peter
"Michael" wrote:

> hi,
> can anyone advise as to which formula would be able to find the highest
> ("=MAX(A\$:T\$)")value in a row of cells and once identified return the column
> header?  thanks for any help!
>
>
>
```
 0
8/22/2006 12:49:02 PM
```Ah well, at last one for two maxs in row if you need it

paste to u 4 and copy

Peter
"Michael" wrote:

> hi,
> can anyone advise as to which formula would be able to find the highest
> ("=MAX(A\$:T\$)")value in a row of cells and once identified return the column
> header?  thanks for any help!
>
>
>
```
 0
8/22/2006 1:11:01 PM
```Dave,
Thank you very much for your response.  If there is more than 1 cell that
has is equal to the "highest" value, will this also display the subsequent
col. hdrs?
"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:44EA7625.2B2C5AE2@verizonXSPAM.net...
> =index(1:1,match(max(2:2),2:2,0))
>
> Will find the first largest value in row 2 and return the header from row
> 1.
>
> Michael wrote:
>>
>> hi,
>> can anyone advise as to which formula would be able to find the highest
>> ("=MAX(A\$:T\$)")value in a row of cells and once identified return the
>> column
>> header?  thanks for any help!
>
> --
>
> Dave Peterson

```
 0
mriorda2 (5)
8/26/2006 7:50:24 PM
```Nope.  It just displays the first match.

Michael wrote:
>
> Dave,
> Thank you very much for your response.  If there is more than 1 cell that
> has is equal to the "highest" value, will this also display the subsequent
> col. hdrs?
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:44EA7625.2B2C5AE2@verizonXSPAM.net...
> > =index(1:1,match(max(2:2),2:2,0))
> >
> > Will find the first largest value in row 2 and return the header from row
> > 1.
> >
> > Michael wrote:
> >>
> >> hi,
> >> can anyone advise as to which formula would be able to find the highest
> >> ("=MAX(A\$:T\$)")value in a row of cells and once identified return the
> >> column
> >> header?  thanks for any help!
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson
```
 0
petersod (12004)
8/26/2006 10:48:10 PM
```Dave,
thanks again for your attention and response to what is a lack of my ability
to figure this out.  Can you offer a solution for this?
"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:44F0CFAA.B28FB99D@verizonXSPAM.net...
> Nope.  It just displays the first match.
>
> Michael wrote:
>>
>> Dave,
>> Thank you very much for your response.  If there is more than 1 cell that
>> has is equal to the "highest" value, will this also display the
>> subsequent
>> col. hdrs?
>> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
>> news:44EA7625.2B2C5AE2@verizonXSPAM.net...
>> > =index(1:1,match(max(2:2),2:2,0))
>> >
>> > Will find the first largest value in row 2 and return the header from
>> > row
>> > 1.
>> >
>> > Michael wrote:
>> >>
>> >> hi,
>> >> can anyone advise as to which formula would be able to find the
>> >> highest
>> >> ("=MAX(A\$:T\$)")value in a row of cells and once identified return the
>> >> column
>> >> header?  thanks for any help!
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson

```
 0
mriorda2 (5)
8/31/2006 12:48:39 AM
```Maybe you can use one of the lookup formulas on Chip Pearson's page.

I'd look at the arbitrary lookup section.

Michael wrote:
>
> Dave,
> thanks again for your attention and response to what is a lack of my ability
> to figure this out.  Can you offer a solution for this?
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:44F0CFAA.B28FB99D@verizonXSPAM.net...
> > Nope.  It just displays the first match.
> >
> > Michael wrote:
> >>
> >> Dave,
> >> Thank you very much for your response.  If there is more than 1 cell that
> >> has is equal to the "highest" value, will this also display the
> >> subsequent
> >> col. hdrs?
> >> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> >> news:44EA7625.2B2C5AE2@verizonXSPAM.net...
> >> > =index(1:1,match(max(2:2),2:2,0))
> >> >
> >> > Will find the first largest value in row 2 and return the header from
> >> > row
> >> > 1.
> >> >
> >> > Michael wrote:
> >> >>
> >> >> hi,
> >> >> can anyone advise as to which formula would be able to find the
> >> >> highest
> >> >> ("=MAX(A\$:T\$)")value in a row of cells and once identified return the
> >> >> column
> >> >> header?  thanks for any help!
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson
```
 0
petersod (12004)
8/31/2006 1:05:58 AM
```Dave,
I hate to be the reason the folks with your talent might become "unnerved"
with involving yourself providing direction and knowledge to one, myself, a
dribbling idiot, which now has to ask you...how does one find Chip Pearson's
page?  Thanks again...100 thanks for your patience!!
Michael
"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:44F635F6.EE03AE84@verizonXSPAM.net...
> Maybe you can use one of the lookup formulas on Chip Pearson's page.
>
> I'd look at the arbitrary lookup section.
>
> Michael wrote:
>>
>> Dave,
>> thanks again for your attention and response to what is a lack of my
>> ability
>> to figure this out.  Can you offer a solution for this?
>> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
>> news:44F0CFAA.B28FB99D@verizonXSPAM.net...
>> > Nope.  It just displays the first match.
>> >
>> > Michael wrote:
>> >>
>> >> Dave,
>> >> Thank you very much for your response.  If there is more than 1 cell
>> >> that
>> >> has is equal to the "highest" value, will this also display the
>> >> subsequent
>> >> col. hdrs?
>> >> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
>> >> news:44EA7625.2B2C5AE2@verizonXSPAM.net...
>> >> > =index(1:1,match(max(2:2),2:2,0))
>> >> >
>> >> > Will find the first largest value in row 2 and return the header
>> >> > from
>> >> > row
>> >> > 1.
>> >> >
>> >> > Michael wrote:
>> >> >>
>> >> >> hi,
>> >> >> can anyone advise as to which formula would be able to find the
>> >> >> highest
>> >> >> ("=MAX(A\$:T\$)")value in a row of cells and once identified return
>> >> >> the
>> >> >> column
>> >> >> header?  thanks for any help!
>> >> >
>> >> > --
>> >> >
>> >> > Dave Peterson
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson

```
 0
mriorda2 (5)
9/6/2006 1:44:33 AM
```Oopsie...

http://cpearson.com/excel/lookups.htm

But if you use google to search the *excel* newsgroups, you would have found
thousands of hits to Chip's site.  It's quite popular.  You may want to bookmark
it for other stuff.

Michael wrote:
>
> Dave,
> I hate to be the reason the folks with your talent might become "unnerved"
> with involving yourself providing direction and knowledge to one, myself, a
> dribbling idiot, which now has to ask you...how does one find Chip Pearson's
> page?  Thanks again...100 thanks for your patience!!
> Michael
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:44F635F6.EE03AE84@verizonXSPAM.net...
> > Maybe you can use one of the lookup formulas on Chip Pearson's page.
> >
> > I'd look at the arbitrary lookup section.
> >
> > Michael wrote:
> >>
> >> Dave,
> >> thanks again for your attention and response to what is a lack of my
> >> ability
> >> to figure this out.  Can you offer a solution for this?
> >> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> >> news:44F0CFAA.B28FB99D@verizonXSPAM.net...
> >> > Nope.  It just displays the first match.
> >> >
> >> > Michael wrote:
> >> >>
> >> >> Dave,
> >> >> Thank you very much for your response.  If there is more than 1 cell
> >> >> that
> >> >> has is equal to the "highest" value, will this also display the
> >> >> subsequent
> >> >> col. hdrs?
> >> >> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> >> >> news:44EA7625.2B2C5AE2@verizonXSPAM.net...
> >> >> > =index(1:1,match(max(2:2),2:2,0))
> >> >> >
> >> >> > Will find the first largest value in row 2 and return the header
> >> >> > from
> >> >> > row
> >> >> > 1.
> >> >> >
> >> >> > Michael wrote:
> >> >> >>
> >> >> >> hi,
> >> >> >> can anyone advise as to which formula would be able to find the
> >> >> >> highest
> >> >> >> ("=MAX(A\$:T\$)")value in a row of cells and once identified return
> >> >> >> the
> >> >> >> column
> >> >> >> header?  thanks for any help!
> >> >> >
> >> >> > --
> >> >> >
> >> >> > Dave Peterson
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson
```
 0
petersod (12004)
9/6/2006 2:08:43 AM
```Dave,
Many thanks!!  This one is a bit over my head.  I'll give myself a week to
play with this and see if I eventually grasp.
Thank you.
Michael R

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:44FE2DAB.AA10C55@verizonXSPAM.net...
> Oopsie...
>
> http://cpearson.com/excel/lookups.htm
>
> But if you use google to search the *excel* newsgroups, you would have
> found
> thousands of hits to Chip's site.  It's quite popular.  You may want to
> bookmark
> it for other stuff.
>
> Michael wrote:
>>
>> Dave,
>> I hate to be the reason the folks with your talent might become
>> "unnerved"
>> with involving yourself providing direction and knowledge to one, myself,
>> a
>> dribbling idiot, which now has to ask you...how does one find Chip
>> Pearson's
>> page?  Thanks again...100 thanks for your patience!!
>> Michael
>> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
>> news:44F635F6.EE03AE84@verizonXSPAM.net...
>> > Maybe you can use one of the lookup formulas on Chip Pearson's page.
>> >
>> > I'd look at the arbitrary lookup section.
>> >
>> > Michael wrote:
>> >>
>> >> Dave,
>> >> thanks again for your attention and response to what is a lack of my
>> >> ability
>> >> to figure this out.  Can you offer a solution for this?
>> >> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
>> >> news:44F0CFAA.B28FB99D@verizonXSPAM.net...
>> >> > Nope.  It just displays the first match.
>> >> >
>> >> > Michael wrote:
>> >> >>
>> >> >> Dave,
>> >> >> Thank you very much for your response.  If there is more than 1
>> >> >> cell
>> >> >> that
>> >> >> has is equal to the "highest" value, will this also display the
>> >> >> subsequent
>> >> >> col. hdrs?
>> >> >> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
>> >> >> news:44EA7625.2B2C5AE2@verizonXSPAM.net...
>> >> >> > =index(1:1,match(max(2:2),2:2,0))
>> >> >> >
>> >> >> > Will find the first largest value in row 2 and return the header
>> >> >> > from
>> >> >> > row
>> >> >> > 1.
>> >> >> >
>> >> >> > Michael wrote:
>> >> >> >>
>> >> >> >> hi,
>> >> >> >> can anyone advise as to which formula would be able to find the
>> >> >> >> highest
>> >> >> >> ("=MAX(A\$:T\$)")value in a row of cells and once identified
>> >> >> >> return
>> >> >> >> the
>> >> >> >> column
>> >> >> >> header?  thanks for any help!
>> >> >> >
>> >> >> > --
>> >> >> >
>> >> >> > Dave Peterson
>> >> >
>> >> > --
>> >> >
>> >> > Dave Peterson
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson

```
 0
mriorda2 (5)
9/7/2006 2:00:20 AM

Similar Artilces:

Setting series values on Excel Chart
Hi I'm struggling very hard in getting beyond this error when trying to use VB to generate a chart from data previously inserted into the sheet. Unable to set the Values property of the Series class. The code works absolutely fine in XL2007 but I am having to port it back to 2003 and the inference is that its empty values that cause it to hiccup - something I cannot avoid. My code is: 1. Retrieve selected data from database and place it at the top of the sheet 2. Add The Chart 3. Iterate through the data (amount can be variable depending upon user inputs) For j as i...

Convert single colum/multiple rows to multiple colums.
Hi, I have a .dat file when opened with Excel it has 1 column and 7 rows per entry. I would like to delete some rows and convert the rest to something like. Any chance this can be done. It's quite large. 51,793 rows. TIA Jeff Col 1 Col 2 Col 3 Name Date Lenny Kravitz - 2000 - Greatest Hits -- Table: {2} { "music" "Name", "06/04/2008", "Lenny Kravitz - 2000 - Greatest Hits", }, --Table: {3} etc. etc. ...

Timestamp value of GINA window presentation
I am currently troubleshooting slow logon times for a client with computer infrastructure spread across the globe. Before I start invesagating I want to create a baseline for logon times. I have modified the clients logon script to record when it starts and when it ends. I am using the computers tick count to record how long the computer has been on, but realize that there is a space of time between the user authenicating to the computer (control-alt-delete) and when the logon script starts, if the customer turns his computer on and then leave for an extended period of time I ...

how to query my web site from VBA and return a value to VBA
Hello All, From VBA I would like send a value to my web site, and have it return a value. I've learned how to use FollowHyperlink to send a value to an ASP script, but how can the ASP script send a value back to VBA?? Thanks, Brian Austin, TX You can use xmlhttp to make a request to your web page: '********************************************************* Sub Tester() MsgBox WebResponse("http://www.mydomain.com/myactualpage.asp? info=3Dblah") End Sub Private Function WebResponse(sURL As String) As String Dim XmlHttpRequest As Object Se...

data value in Form field if no table entry
I have a form with a field which pulls through and concentenates 2 fields called [ContactFirstname] and [ContactLastName]from my table There are however some customers for whom I do not have names and therefore instead I would like Sir/Madam to appear in the field in the form I think I have seen this done somewhere using ELSE? but can't find it Any help/ideas gratefully received Perhaps something like this: Nz(Trim([ContactFirstname] & " " + [ContactLastName]), "Sir/Madam") -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access use...

Summing distinct rows in same cell
Sorry for the bad title I'm not sure hte best way to sum up this issue. Fruit Price Total Apples 0.69 40 Bananas 0.34 38 Lemons 0.55 15 Oranges 0.25 25 Apples 0.5 10 Pears 0.59 40 Almonds 2.8 10 Cashews 3.55 16 Peanuts 1.25 20 Walnuts 1.75 12 Apples 0.5 5 Given the data above I'm trying to sum the totals for only the Appl rows. So basically I need to search the Fruit column find the row that have "Apples" and then sum their corresponding Totals, giving m 55. I've been playing with this for hours with Lookups and Indexes bu am not really getting anywhere. Also, I can...

Save As using contents of Cell in Name
Can someone help with code that changes the "save As" file name to combination of the original File Name + the contents of a cell, an saves file in same folder as the original. E.G. Original File Name= Timesheet Cells B1= Location Cell C1 = Date in format 3-5-2004 Cell A1 = concatenate(B1,C1) Fle Name will therefore be "Timesheet Location 3-5-2004" The other problem is with the date. When concatenating, how do I forma the date to avoid it looking like "Location 38051" Any help much appreciated Nic -- Message posted from http://www.ExcelForum.com Hi for c...

How can I choose alternate rows in a column?
My requirement is to be able to create a column whose elements consist of difference between adjacent elements in a column, say column A. If I can choose alternate elements and create 2 new columns then I can just subtract the 2 columns easily. Huh? "pnair" <pnair@discussions.microsoft.com> wrote in message news:D47AD012-084B-49C6-8672-5067E8455D9E@microsoft.com... > My requirement is to be able to create a column whose elements consist of > difference between adjacent elements in a column, say column A. If I can > choose alternate elements and create 2 new colum...

Assign values for one column to another.
Hi I have in column T certain numbers and texts that that I require to assign a value to as below, in the adjacent column. Again any pointers would be much appreciated. Kind Regards Celticshadow T U 1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 0 10 F 10 UR 10 U 10 R 10 S 10 L 10 P 10 PU 10 BD 10 D 10 Well, imagine that two-column table occupies cells Y1:Z20. Put this formula in U1: =3DVLOOKUP(T1,Y\$1,Z\$20,2,0) and copy down. Hope this helps. Pete On Oct 14, 4:26=A0pm, Celticshadow <Celticsha...@discussions.microsoft.com> wrote: > Hi > >...

Count on cell for each time it is changed
I would like to count the number of times a cell has been amended/ changed e.g cell b2 has the word red it it, in cell c2 i would like a counter for everytime the cell in b2 has changed, at the end of the day you get final number. Is this at all possible? Jelinek, You can do it by putting the following VBA macro in your sheet: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "\$B\$2" Then Cells(2, 3) = Cells(2, 3) + 1 End If End Sub Art "Jelinek" wrote: > I would like to count the number of times a cell has bee...

Excel formulation to automate values
Hi there, I have a little problem on arranging a small multi-worksheet excel here. Let me explain in a short way then give some details on it. I am trying to make an offers recordsheet which has two worksheets in it. One for products(and their base prices) and an offer sheet. I would like to use a bit dynamic data here. So when I type the name of the product excel would give me the price from other worksheet. in details; Prices worksheet is something like: A B 1 Product BasePrice 2 mouse 5 3 keyboard 8 4 ... And the ...

Cells Fill Automatically on Another Workbook
I've created what we'll call a seed worksheet to be used over and over for different clients. I have linked its cells to another workbook. As the originating seed worksheet directs its cell data to a specific cell on another workbook, how can I accomplish the workbook data not being overwritten but the new incoming data default to the next unused cell in the column? i.e. If the original seed worksheet cell B1 links to the worksheet cell A1, I would like the next instance of creating a new customer and his B1 information on his use of the seed worksheet to populate onto th...

hide/change color of selected row headings
Is it possible to hide the row heading numbers for selected rows (i.e. rows 51 and greater) for just the selected sheet? I'd like to have a color with no row heading number appear that matches the fill color I select for the adjacent cells. Secondly, any ideas as to how I can prevent the user from scolling further down than a certain point (i.e. row 51). Thanks in advance. Joe Row headings are either on or off, you can't hide some. However, you can achieve a similar effect by hiding *all* headings (Tools/Options/General) and putting the numbers 1:51 in A1:A51. to limit scro...

Concatenating cells but excluding blanks
Hello, I am trying to create a result field, concatenating populated cells from the previous 12 columns on that line, but excluding blank cells and putting a * delimiting character between each instance - please find below a 4 column example. ID 1 2 3 4 Result Z A C D A*C*D Y B C B*C X A B D A*B*D Each of the 10,000 lines of the spreadsheet is different - there are at least 5 blank cells on each line Any help gratefully received. I am working in Excel 2007 Many thanks. Bob Try this: http://img690.imageshack.us/img690/5826/nonamee.png Micky "Bob Fr...

Can't insert rows
I am using Excel 2007 and have just loaded a spreadsheet created in a previous version which is running in Compatibility mode. When I try to insert a row I get an message "Cannot shift objects off sheet". How do I insert rows on this sheet? No problem with another workbook loaded at the same time which was created in 2007. See if this helps http://support.microsoft.com/default.aspx?kbid=211769 "Cannot shift objects off sheet" error message when you hide columns in Excel -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my...

Copy Data from One Group of Cells to Another Group
I have five columns of data on two different sheets in the same workbook. One set of columns is sorted in ascending date order the other in descending date order. When I enter data into the last row of Sheet 1, I need the data in that row in columns A, B, C and D to be copied into Sheet 2 columns A, C, D and E in a newly inserted row 14. Is this possible with the use of a macro? I can find the last cell in Sheet 1, but then need to go up one row and back to column A. I am having difficulty with that. Thanks is advance for any assistance offered! /s/ Alan Auerbach On Sat, 26 May 2007, ...

Copy cell contents, then paste into the same cell with other text.
Hi! I tried a search first and couldn't find anything like this. My spreadsheet has a column for shipping that takes a series like this for each product: ?0.0*0.13.2*d*0x0x0:07:24:04 Following the question mark is the handling charge (0.0 in this example). This is followed by an * and then the weight of the item (0.13.2 in this example which is 13.2 ounces) I have a list of product weights in a colum with just pounds and ounces. I need to copy that information, then paste it into the weight area of the string above and then paste those modified contents back into t...

How to select other random cells
I have a 2 part question. I want to have a 1 question survey randomly filled out by 200 people. Column A lists the 1 questions. Columun b has ABCDE listed in cells B6 to b10. I would like 200 samples in columns c, d, e, etc. How do I set this up so that they randomly Coose B6 to B 10. Second part. On another spreadsheet, I have a similar situation, but the user has 10 answers to select from and I want them to randomly select all that apply. How would I do this? Thanks! ...

How do I get total value data labels in a stacked bar chart?
I have a 3-D stacked bar chart with four series and I want to have the total value in each category be displayed in a data label. Can I do this, and if so, how?? Hi, This should help http://www.andypope.info/charts/StackColTotal.htm Cheers Andy blemerson wrote: > I have a 3-D stacked bar chart with four series and I want to have the total > value in each category be displayed in a data label. Can I do this, and if > so, how?? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Thanks, Andy. The CFO is thrilled. Heather "Andy Pope" wrote: > Hi, >...

Number Rows on a report?
It would be anice addition if I could add numbers (a count) in front of each row on my filtered report. Any easy way to do this. I was thinking an unbound text box with something in the control source - I just don't know the something :) Any help here will be appreciated. Thanks in advance On Wed, 20 Jun 2007 14:07:27 -0700, Dave wrote: > It would be anice addition if I could add numbers (a count) in front of each > row on my filtered report. > > Any easy way to do this. > > I was thinking an unbound text box with something in the control source - I > just...

How do I call individual cell data from an Excel sheet into Powerpoint or Word?
Ok, I have an Excel work sheet done up to calculate discounts given to employees based on their employer. I now need to make a flyer in either Word or PowerPoint (or another program if needed) but I'd like for it to pull the data from individual cells in the Excel sheet. For instance in the midst of the graphics and flyer text that will remain the same for every flyer I'd like to be able to tell it to refer to Excel document, and then pull all the information for say, the company in row 7 (since 7 is the first company listed) It would need to pull the text (co. name) from A7, sug. r...