+ Reply to Thread
Results 1 to 3 of 3

show cell blank instead of 0 or error after index match formula

  1. #1
    Registered User
    Join Date
    02-27-2012
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    65

    show cell blank instead of 0 or error after index match formula

    i have been using these two formula; both are working correctly but need adjustment; if i use first formula all blank cells are shown as "0"; i have tried conditional formating, custom format to hide zeros but its not happening.
    =IFERROR(INDEX($F10:$M10,MATCH(P$7,$F$7:O$7,0)),"0")

    in second formula if result is shown blank my subsquent formula linked to the cell gave value error.
    =IFERROR(INDEX($F10:$M10,MATCH(P$7,$F$7:O$7,0)),"")

    this is my linked formula which got effected if cell is shown blank through formula
    =IF(SUM(F10:AJ10)=0,"",IF(SUMPRODUCT(($F$6:$AJ$6=TRANSPOSE(Rtg!$C$2:$I$2))*($F10:$AJ10)*TRANSPOSE(OFFSET(Rtg!$C$3,MATCH($A10,Rtg!$A$3:$A$50,0)-1,0,1,7)))/30*$E10=0,"",SUMPRODUCT(($F$6:$AJ$6=TRANSPOSE(Rtg!$C$2:$I$2))*($F10:$AJ10)*TRANSPOSE(OFFSET(Rtg!$C$3,MATCH($A10,Rtg!$A$3:$A$50,0)-1,0,1,7)))/30*$E10))

    need help please

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,147

    Re: show cell blank instead of 0 or error after index match formula

    =IFERROR(INDEX($F10:$M10,MATCH(P$7,$F$7:O$7,0)),0) format cells as 0;0;;@ if you want to hide zeros
    Last edited by JohnTopley; 04-23-2016 at 04:23 AM.

  3. #3
    Registered User
    Join Date
    02-27-2012
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    65

    Re: show cell blank instead of 0 or error after index match formula

    thanks
    it working now

+ 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] Need cell with formula to show as blank instead of error message when no data is entered
    By jaiverson in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-05-2016, 09:15 PM
  3. [SOLVED] Blank Cell with Error If/Index/Match/Iserror
    By jespo1351 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-13-2014, 10:19 AM
  4. I want to exclude blank cell in index match formula with multiple criteria
    By NOOR8225 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-16-2013, 06:51 AM
  5. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  6. [SOLVED] An INDEX MATCH formula has blank cell - need to ignore or delete
    By rls231 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-26-2013, 11:13 PM
  7. Index Match: Show blank instead of #NA for no match
    By cedarhill in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-20-2011, 03:21 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