+ Reply to Thread
Results 1 to 6 of 6

index match , 0 instead of N/A when no result

  1. #1
    Registered User
    Join Date
    04-18-2017
    Location
    NW,UK
    MS-Off Ver
    MS Office 2013
    Posts
    89

    index match , 0 instead of N/A when no result

    Hello Forum

    I have the below forumla if there is no result i get a N/A error which for what i usually use it for works for me, but i now need it to display a 0 instead of a N/A if there is no result.

    Thank you in advance


    Formula: copy to clipboard
    Please Login or Register  to view this content.

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: index match , 0 instead of N/A when no result

    You can do this:

    =IFERROR(INDEX(D:D,MATCH(A1&B1,A:A&B:B,0)),0) Ctrl Shift Enter

    or this non-array version:

    =IFERROR(INDEX(D:D,INDEX(MATCH(A1&B1,A:A&B:B,0),0)),0)

    One thing to note is not to use whole column references as this may slow down your workbook. Try to limit your ranges to a smaller range such as A$2:A$100000.

  3. #3
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: index match , 0 instead of N/A when no result

    After looking at your formula a little bit closer, there is probably a better way to do what you are trying to do.

    A1&B1 will always be in A&B.

    Your formula in post #1 will work the same as =D1

  4. #4
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: index match , 0 instead of N/A when no result

    IFERROR is likely your best option
    Last edited by Zer0Cool; 11-22-2017 at 11:33 AM. Reason: Misunderstood question at first

  5. #5
    Registered User
    Join Date
    04-18-2017
    Location
    NW,UK
    MS-Off Ver
    MS Office 2013
    Posts
    89

    Re: index match , 0 instead of N/A when no result

    Helloo 63falcondude

    The non-array version worked perfectly, the array version for some strange reason brought the result 0 for every entry.

    Thanks for the help
    Last edited by AliGW; 11-22-2017 at 12:32 PM. Reason: Unnecessary quotation removed.

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: index match , 0 instead of N/A when no result

    Glad to help. Thanks for the rep!

    The array formula has to be entered using Ctrl Shift Enter instead of just Enter. If you do not enter an array formula with CSE, it will not work as intended.

    That being said, I prefer to use a non-array formula over an array equivalent whenever possible.

    I am still curious as to why you need an INDEX MATCH formula since A1 will always be in column A and B1 will always be in column B but you seem to be happy with what you have and that is what matters.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] INDEX/MATCH generates incorrect result by referencng nearest cell if match not found
    By aglawrence in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-29-2017, 08:59 AM
  2. Index/Match for Filtered result
    By FarEast07 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-03-2016, 04:51 AM
  3. [SOLVED] Index/Match - More than 1 Result
    By LJenny in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 10-13-2015, 11:08 AM
  4. [SOLVED] Add Text to an Index/Match Result?
    By Pooger in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-28-2015, 11:49 AM
  5. [SOLVED] Index/Match result should again search for zero result
    By ursanil in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-07-2014, 04:45 AM
  6. index match, ..... skip result if blank value ...... jump to next match
    By gehawk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-08-2013, 04:42 AM
  7. [SOLVED] Index Match if result is not something
    By Jaron_t in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-19-2012, 01:19 PM

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