+ Reply to Thread
Results 1 to 10 of 10

Search range and return column letter if value is greater than zero

  1. #1
    Registered User
    Join Date
    11-20-2010
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    7

    Exclamation Search range and return column letter if value is greater than zero

    Hi everyone,

    I need help with the following:
    I need to search a range and return the column letter if the value in the cell is greater than zero. it needs to return the first column letter (starting from column F to A in example below).

    example:
    A B C D E F
    0 2 0 5 0 0


    So if searching through the range, the result should return 'D'

    this is my first contribution to the forum, your help is much appreciated,
    thanks
    Marwan

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Search range and return column letter if value is greater than zero

    A few approaches you could use but if we assume A:F could vary then perhaps:

    Please Login or Register  to view this content.
    where A1:F1 hold original values

  3. #3
    Registered User
    Join Date
    11-20-2010
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Search range and return column letter if value is greater than zero

    thanks this is perfect !!!

  4. #4
    Registered User
    Join Date
    11-20-2010
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Search range and return column letter if value is greater than zero

    Given the previous ask/result, I need to sum a range. but that range is now defined by the result of the formula you provided earlier.

    i always think its best explained in an example:

    (i decided to attach it for mre clarity)

    Please let me know if you need any further details ,
    thanks again
    Marwan
    Attached Files Attached Files

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Search range and return column letter if value is greater than zero

    Not sure if I've followed or not but perhaps:

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    10-25-2012
    Location
    Monterrey, Mexico
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Search range and return column letter if value is greater than zero

    Iīm doing basically the same thing but would like to find the last column in which "0" appears (and return the column # not as a letter)

    I J K L M N O P Q R S T
    0 4 7 0 8 2 3 0 1 8 2 0
    1 1 1 1 2 3 4 5 2 1 2 1
    8 1 9 2 3 7 7 6 0 0 0 0

    For instance in the last row would have Q (column #17) as a result.

    Thanks

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,568

    Re: Search range and return column letter if value is greater than zero

    For your third row, the last 0 is in column T, is it not? Did you want to return T or 20?

    =LOOKUP(2,1/(I2:T2=0),COLUMN(I1:T1)) should work (assuming you do want the last 0 like you said)
    ChemistB
    My 2Ē

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  8. #8
    Registered User
    Join Date
    10-25-2012
    Location
    Monterrey, Mexico
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Search range and return column letter if value is greater than zero

    Quote Originally Posted by ChemistB View Post
    For your third row, the last 0 is in column T, is it not? Did you want to return T or 20?

    =LOOKUP(2,1/(I2:T2=0),COLUMN(I1:T1)) should work (assuming you do want the last 0 like you said)
    I didnīt explain my self.
    What I want to do really is to count how many "0"īs appear beginning from the LAST row
    In my example would be:

    1 (for the first row, since it appears in T)
    0 (for the second row, since thereīs not a 0 in the last row)
    4 (for the third row, since there are 4 continuos zeros since the last row -they start appearing in Q-)

  9. #9
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,568

    Re: Search range and return column letter if value is greater than zero

    Try this

    =COLUMN(T1)-LOOKUP(2,1/(I1:T1<>0),COLUMN(I1:T1))

  10. #10
    Registered User
    Join Date
    10-25-2012
    Location
    Monterrey, Mexico
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Search range and return column letter if value is greater than zero

    ok thanks a lot!
    It worked perfect

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1