Need help with #N/A display for IF Function

[accounting1005]

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? 

[#2]

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.

[#3]

@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.

[#4]

@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

[#5]

@m_tarler Thanks for editing the formula! When I copied the Monday sheet with your code to duplicate the other weekdays, and reworked the sum on the summary sheet, I ended up getting value errors. I feel like I made this more complicated as I might've asked for help with the wrong thing. In retrospect, I should've mentioned the goal that got me to these errors in the first place. In the old file, my managers would have to select the employee positions everyday for the codes to work. I wanted to make this easier for them by having them set the information on the Weekly Summary sheet. I was able to make some progress with the edits but I think it would be best to break down how I got here to my original post. 

I will have to message you the file again but here is what I did. 

On the Weekly Summary sheet, I added column D (Position) as it was not there before. I also turned column B,C,D into filters so the information can alphabetize should the team need to add new staff and sort the names.

On the daily sheets, column C had always been there but it had a drop down function that lists the positions. You can see this on Tues - Sun. I was able to remove the dropdown list and use "='Weekly Summary'!D4" as a reference for that column. However, once you get to the cells that don't have information, the zeros and errors will show up. I was able to format column C to hide the zeros but the #N/As for the rest of the row is still there and gets reflected on the summary sheet. This was how I got to the original issue I inquired about.