+ Reply to Thread
Results 1 to 6 of 6

MATCH function - unexpected #NA result

  1. #1
    Registered User
    Join Date
    03-08-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    17

    MATCH function - unexpected #NA result

    My goal is to solve this equation:
    {=INDEX(Sales_Data,MATCH(C3&A5,SalesDataB:B&SalesData!AD:AD,0),41)}

    The MATCH function is producing a #NA result.

    When I use the formula wizard for just the MATCH function..

    {=MATCH(C3&A5,SalesData!B:B&SalesData!AD:AD,0)}

    ..the wizard shows the formula result (row#) as 3,539. When I go inspect the table, in fact row 3,539 is the right row.

    However, when I hit OK, in the wizarditself, it sheet shows the value as #NA.

    I don't understand what is wrong with MATCH function. Is it the formatting of the cell?

    Help is appreciated.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: MATCH function - unexpected #NA result

    I see a missing ! in the Match range.

    {=INDEX(Sales_Data,MATCH(C3&A5,SalesData!B:B&SalesData!AD:AD,0),41)}


    Does that fix it?
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    03-08-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: MATCH function - unexpected #NA result

    Thank you. However, that was just a typo on my part.

    This is Salesforce data that I've pasted into an Excel tab called SFDC. Below are the actual two functions, again, where the MATCH is producing the #NA.

    {=INDEX(SFDC_Data,MATCH(C3&A5,SFDC!B:B&SFDC!AD:AD,0),41)}
    {=MATCH(C3&A5,SFDC!B:B&SFDC!AD:AD,0)}

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: MATCH function - unexpected #NA result

    I don't think I've ever used this technique without using an internal INDEX() on the joined columns of data. I certainly haven't tried this with entire columns, I would fear performance issues.


    {=INDEX(SFDC_Data, MATCH(C3&A5, INDEX(SFDC!B:B&SFDC!AD:AD, 0) ,0),41)}

  5. #5
    Registered User
    Join Date
    03-08-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: MATCH function - unexpected #NA result

    and it works.

    muchas gracias.

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: MATCH function - unexpected #NA result

    I've marked this thread as SOLVED for you.
    Next time, select Thread Tools from the links above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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