Index / match
Results 1 to 5 of 5

Thread: Index / match

  1. #1
    Board Regular
    Join Date
    May 2007
    Location
    North Vancouver, BC, Canada
    Posts
    311
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Index / match

    Here is the data which I am trying to extract the dollar amount in D4 from.

    Excel 2016 (Windows) 32 bit#E0E0F0 " />#E0E0F0 ;text-align: center;color: #201116">
    ABCDEFGHIJKLMNOP
    1Tax Credit TypeFederalNLPENSNBONMBSKABBCYTNTNU
    215%8.70%9.80%8.79%9.68%5.05%10.80%10.50%10%5.06%6.40%5.90%4%
    3-3-5-1-6-4-7
    4Basic personal amount (1)(5) $12,069$9,414$9,160$8,481$10,264$10,582$9,626$16,065$19,369$10,682$12,069$14,811$16,000

    #E0E0F0 ;color: #201116">tax_credits_web



    Here is my formula which is returning the #N/A error. Does anyone know what I can do to resolve this? Thanks!

    Code:
    =INDEX(D2:P4,MATCH(TRUE,ISNUMBER(SEARCH(A2:A4,basic)),0),MATCH(TRUE,ISNUMBER(SEARCH(D1:P1,Federal)),0))

  2. #2
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    2,519
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Need help with INDEX / MATCH

    ABCDEFGHIJKLMNOP
    1Tax Credit TypeFederalNLPENSNBONMBSKABBCYTNTNU
    215%8.70%9.80%8.79%9.68%5.05%10.80%10.50%10%5.06%6.40%5.90%4%
    3-3-5-1-6-4-7
    4Basic personal amount (1)(5)$12,069 $9,414 $9,160 $8,481 $10,264 $10,582 $9,626 $16,065 $19,369 $10,682 $12,069 $14,811 $16,000
    5
    612069

    Sheet1



    Array Formulas
    CellFormula
    B6{=INDEX(D2:P4,MATCH(TRUE,ISNUMBER(SEARCH("basic",A2:A4)),0),MATCH(TRUE,ISNUMBER(SEARCH("Federal",D1:P1)),0))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  3. #3
    Board Regular
    Join Date
    May 2007
    Location
    North Vancouver, BC, Canada
    Posts
    311
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help with INDEX / MATCH

    Scott T, thank you so much! As a follow-up, and this is a hypothetical question, what would happen if there were two occurrences of the word 'Federal' in row A, ie. let's say that 'Federal' was also found in cell H1? Is there a way to prevent the formula from returning the #N/A error? Thanks!

  4. #4
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    2,519
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Need help with INDEX / MATCH

    Match would find the first occurrence of Federal and still return 12,069.

    You should only get the N/A if one of the words you search for is not found or you do not use CTRL+SHIFT+ENTER.

    If you do not use search then you do not need CSE

    You could use wildcards
    Code:
    =INDEX(D2:P4,MATCH("*Basic*",A2:A4,0),MATCH("federal",D1:P1,0))
    or enter the whole text
    Code:
    =INDEX(D2:P4,MATCH("Basic personal amount (1)(5)",A2:A4,0),MATCH("federal",D1:P1,0))
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  5. #5
    Board Regular
    Join Date
    May 2007
    Location
    North Vancouver, BC, Canada
    Posts
    311
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help with INDEX / MATCH

    Thanks again! I'm always learning.

    Cheers!

Some videos you may like

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
  •