Forum Discussion

accounting1005's avatar
accounting1005
Copper Contributor
Sep 27, 2024

Need help with #N/A display for IF Function

Hello Experts,

 

I'm reworking a sheet that I did not create and need help with replacing the #N/A display with a blank when the referencing cell is blank. Is this possible? The formula works as intended so it's technically correct, however, I want this formula to work when rows are also blank. This sheet is used to help us calculate employee tips and pay. 

Please see image of the summary sheet. The rows calculate the information from the daily sheets. The daily sheets reference the Jobs table.

On the daily sheet, column C references the Jobs table on the summary sheet. Column O references column C to calculate what happens to columns P - U. The formula for column O is: 

=IF(ISBLANK(C5),"",INDEX(JOB_DATA,MATCH(C5,JOB_NAME,0),4))

 

JOB_DATA is the entire row in the Jobs table. JOB_NAME is the Position column.

 

The blank rows on the summary and daily sheets are there for us to add/remove employees. Is there a way for these #N/A cells to show as a blank? 



 

 

  • m_tarler's avatar
    m_tarler
    Sep 27, 2024

    accounting1005 I apologize I missed that "4" in the column for the index.  you just need to add INDEX or CHOOSECOLS so the following works:

    =XLOOKUP(C5,JOB_NAME,CHOOSECOLS(JOB_DATA,4),"")

    Or better yet I named your Jobs table (Table Design tab) JOBS so now you can just call out the column of interest (actually you could before but it was named like Table5 so it didn't mean a lot to the reader. But now:

    =XLOOKUP(C5,JOB_NAME,JOBS[Tip Share],"")

     so you can clearly see you are returning "Tip Share" value out of the JOBS table.

    See attached I updated Monday

  • m_tarler's avatar
    m_tarler
    Steel Contributor

    This is real hard to tell without having the sheet to look at but that formula is correct if column C is blank and will be filled in. If, however, column C is a formula like =A5 or =IF(ISBLANK(A5),"",A5) or something else then technically it is NOT Blank. maybe try =IF(C5="","", ...

    on another note you should consider using XLOOKUP instead of INDEX( MATCH()) and then you can also set a if_not_found value = "" so something like:

    =IF(C5="","",XLOOKUP(C5,JOB_NAME,JOB_DATA,""))

    and probably won't even need that condition then so:

    =XLOOKUP(C5,JOB_NAME,JOB_DATA,"")
    othewise maybe attach a sample sheet without private information and we can see better what is going on.

    • accounting1005's avatar
      accounting1005
      Copper Contributor

      m_tarler Thanks for your reply! I'm not advanced with excel, and tried the two suggestions you made but I got a #Spill! error. I couldn't attach a sample sheet because .xlsm is not supported. Is there a different format I should save the sample sheet as or a different way to attached the sheet?

       

      Edit: I think I'm able to send you the sheet via private message. Let me try that instead.

      • m_tarler's avatar
        m_tarler
        Steel Contributor

        accounting1005 I apologize I missed that "4" in the column for the index.  you just need to add INDEX or CHOOSECOLS so the following works:

        =XLOOKUP(C5,JOB_NAME,CHOOSECOLS(JOB_DATA,4),"")

        Or better yet I named your Jobs table (Table Design tab) JOBS so now you can just call out the column of interest (actually you could before but it was named like Table5 so it didn't mean a lot to the reader. But now:

        =XLOOKUP(C5,JOB_NAME,JOBS[Tip Share],"")

         so you can clearly see you are returning "Tip Share" value out of the JOBS table.

        See attached I updated Monday

Resources