+ Reply to Thread
Results 1 to 3 of 3

INDEX/MATCH formula doesn't like blank cells.

  1. #1
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    195

    INDEX/MATCH formula doesn't like blank cells.

    Here is the formula I'm working with:

    =INDEX(Calcs!$AB$5:$AB$200,MATCH(1,INDEX((Calcs!$AB$5:$AB$200<>"")*(Calcs!$Z$5:$Z$200=$B20),),0))

    It works perfectly except when it comes to end of the rows of data and encounters blank cells, it then spits out the dreaded "#N/A" error. Since the data slowly fills as the year goes on, I cannot simply limit the cells it calculates.

    How can I change the formula to not freak-out when encountering blank cells?

    Thanks in advance.
    Last edited by Big.Moe; 05-07-2015 at 12:22 PM.

  2. #2
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    195

    Re: INDEX/MATCH formula doesn't like blank cells.

    Found the solution, it was as simple as placing =IF(Calcs!$AB$5:$AB$200="","", in front of the first INDEX.

    Sorry to have bothered the forum with this.

  3. #3
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: INDEX/MATCH formula doesn't like blank cells.

    If you're simply trying to alleviate the #N/A error, you could use IFERROR, or IF(ISERROR if you are working with Excel 2003:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

+ 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. Index/ Match Formula, How to return blank cell as a blank not 0
    By MDResearcher in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-24-2016, 08:40 PM
  2. [SOLVED] Index Match Formula doesn't recognize output from Right Formula as lookup value.
    By GoGators in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-10-2015, 11:33 AM
  3. Index Match Match Formula DOESN'T WORK WITH TABLES/TABLE NAMES ??
    By Underling in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-04-2014, 09:01 PM
  4. Match function doesn't find BLANK cells?
    By ssudekum in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-31-2014, 07:35 PM
  5. Index Match formula doesn't seem to be working
    By ortmandj in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-17-2013, 08:04 PM

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