Find Index Value of 2nd, 3rd, ect. Mentioned Value

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Find Index Value of 2nd, 3rd, ect. Mentioned Value

  1. #1
    New Member
    Join Date
    Dec 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Find Index Value of 2nd, 3rd, ect. Mentioned Value

     
    Hello - I have data in columns A1:PQ1 and I need to find the position of several different letter/number combinations in the array. For example, when I use:

    =MATCH("XVALS"&"*",$A$1:$PQ$1,0)

    it returns 33 (corresponding to AG1). For the next search I can change $A$1 to $AE$1, but this complicates everything because the indexing does not match the original array. Any help?

    Thanks!

  2. #2
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,036
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Index Value of 2nd, 3rd, ect. Mentioned Value

    Welcome to the Forum!

    C3 (array-entered): =SMALL(IF(ISNUMBER(SEARCH("XVALS",A$1:PQ$1)),COLUMN(A$1:PQ$1)-COLUMN(A$1)+1),ROWS(C$3:C3))

    Excel 2010
    ABCDEFGHIJ
    1XVALS1XVALS2XVALS3XVALS4
    2
    33
    44
    56
    610

    Sheet1



    Last edited by StephenCrump; Dec 7th, 2017 at 01:17 AM.

  3. #3
    New Member
    Join Date
    Dec 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Index Value of 2nd, 3rd, ect. Mentioned Value

    StephenCrump - Thank you for your reply. When I try this, it returns #NUM !. I think it is because the "XVALS" I am looking for contains text. For examples cell AG1 and FV1 on my spreadsheet contain
    "xvals:[0.005" and "xvals:[0.0053"
    Even though these are distinct, an issue arises when I run into the second "xvals:[0.005"

    Thanks

  4. #4
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,036
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Index Value of 2nd, 3rd, ect. Mentioned Value

    The formula in C3 needs to be array-entered, i.e. type the formula and hit the CTRL-SHIFT-ENTER keys together, rather than just ENTER.

    Copy this formula down the column and you shouldn't get #NUM errors until you run out of found values.

    You can wrap the formula in an IFERROR( ... , "") to suppress these errors.

  5. #5
    New Member
    Join Date
    Dec 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Index Value of 2nd, 3rd, ect. Mentioned Value

    Still get #NUM error. I'm using Excel 2013 if that makes a difference. I entered it exactly as you did above, no luck. Also, I would like to avoid using array-enter because I will need to copy similar data into this template and I think it gives me issues when I try and change an array that is being referenced.

  6. #6
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,036
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Index Value of 2nd, 3rd, ect. Mentioned Value

    I'm not sure why yours isn't working, but here's my file for comparison: https://app.box.com/s/dr79pmur4jtb54i3b9w7cedasx5y5een

  7. #7
    New Member
    Join Date
    Dec 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Index Value of 2nd, 3rd, ect. Mentioned Value

      
    It works now! Thanks

User Tag List

Tags for this Thread

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
  •  

 

 
DMCA.com