Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 28

Thread: Find first number in column using match/index function

  1. #1
    Board Regular
    Join Date
    Oct 2004
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Find first number in column using match/index function

    Hello,
    Can someone pls. assist me with providing a formula, ideally using the index/match functions, to locate the first absolute (may have positive or negative values) number in a column? I have added snapshots of how the data would look on the two sheets they are currently located on.

    Thank you in advance.

    Sheet 1

    ID Value
    12345678A NULL
    12345678A 100
    12345678A 150
    12345678B 200

    Sheet2

    ID Expected Result
    12345678A 100

  2. #2
    Board Regular Momentman's Avatar
    Join Date
    Jan 2012
    Location
    Nigeria
    Posts
    3,942
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find first number in column using match/index function

    Like this maybe:

    Excel 2010
    AB
    1IDValue
    212345678ANULL
    312345678A100
    412345678A150
    512345678B200
    6
    712345678A100

    Sheet14



    Array Formulas
    CellFormula
    A7{=INDEX(A2:A5,MATCH(TRUE,ISNUMBER(1*(B2:B5)),0))}
    B7{=INDEX(B2:B5,MATCH(TRUE,ISNUMBER(1*(B2:B5)),0))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    Using Excel 2007 ,2010,2013 Windows 7 - 64bit

    You can be whatever you think you can

  3. #3
    Board Regular
    Join Date
    Oct 2004
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find first number in column using match/index function

    Hello Momentman,

    Thank you for the prompt response. Pls forgive my ignorance but can you pls. explain to me how the formula in cell B7 is linked to the id noted in cell A7? Pls. note that it is not necessary to have a formula to define the id in cell A7 as the ids will be defined as a user input as noted in Sheet 2 in my initial post.

    Pls. advise.

    Best regards,
    maldonadocj
    Last edited by maldonadocj; Jun 3rd, 2014 at 08:19 PM.

  4. #4
    Board Regular
    Join Date
    Oct 2011
    Posts
    3,905
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Find first number in column using match/index function

    Try changing formula in B7 to example below.

    This is an array formula and must be entered with CTRL-SHIFT-ENTER

     ABC
    1IDValue 
    212345678ANULL 
    312345678A100 
    412345678A150 
    512345678B200 
    6   
    712345678A100 
    8   

    Spreadsheet Formulas
    CellFormula
    B7{=INDEX(B2:B5,MATCH(1,ISNUMBER(1*(B2:B5))*(A2:A5=A7),0))}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4

  5. #5
    Board Regular Momentman's Avatar
    Join Date
    Jan 2012
    Location
    Nigeria
    Posts
    3,942
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find first number in column using match/index function

    Quote Originally Posted by maldonadocj View Post
    Hello Momentman,

    Thank you for the prompt response. Pls forgive my ignorance but can you pls. explain to me how the formula in cell B7 is linked to the id noted in cell A7? Pls. note that it is not necessary to have a formula to define the id in cell A7 as the ids will be defined as a user input as noted in Sheet 2 in my initial post.

    Pls. advise.

    Best regards,
    maldonadocj
    I must have answered the wrong question, thought you needed the first number in the series irrespective of IDs.

    AhoyNC's formula will do what you need , this portion handles your query (A2:A5=A7) which is what i didn't have in mine

    Victor
    Using Excel 2007 ,2010,2013 Windows 7 - 64bit

    You can be whatever you think you can

  6. #6
    Board Regular
    Join Date
    Oct 2004
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find first number in column using match/index function

    Hello AhoyNC,

    Thank you very much for your assistance. The formula worked perfectly. However, I have additional nuances that would need to be added. Can you pls. provide me an additional formula that would retrieve the date associated with the numerical value that was returned from your formula? Additionally, is it possible to revise your formula so that in addition to retrieving the numerical value it will also retrieve any numerical values that were imbedded in the string? Pls. see example below:

    A B C
    1 ID Value Date
    2 12345678A NULL 1/15/2013
    3 12345678A 100H 1/25/2013
    4 12345678A 150 1/26/2013
    5 12345678A 200 1/27/2013
    6 12345678B 250 9/29/2013
    7
    8 12345678A 100 1/25/2013

    Best regards,
    maldonadocj
    Last edited by maldonadocj; Jun 4th, 2014 at 06:24 PM.

  7. #7
    Board Regular
    Join Date
    Oct 2004
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find first number in column using match/index function

    Hello Momentman,

    No worries. I appreciate that you followed up. I sent a follow up post to AhoyNC regarding several new additions to the formula provided. Pls. feel free to review my post and let me know if you are also able to address the requests.

    Thank you.
    maldonadocj

  8. #8
    Board Regular
    Join Date
    Oct 2011
    Posts
    3,905
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Find first number in column using match/index function

    Is there any pattern to how the numbers appear in the string. Will they always be at the beginning and 3 digits as in your example?

  9. #9
    Board Regular
    Join Date
    Oct 2004
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find first number in column using match/index function

    Hello AhoyNC,

    Great question. There is no set pattern. The text may also be in front such as "Low100" or both "LM100a". Hope this helps.
    Thank you,
    maldonadocj

  10. #10
    Board Regular
    Join Date
    Oct 2011
    Posts
    3,905
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Find first number in column using match/index function

    Will the numbers always be together as in your post Low100 LM100a. Could you ever have "Low10ab45"?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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