Index Match Formula Errors

Options

This is a two-parter:

First, I have a sheet that references a main roster that contains information such as a person's role, % billable hours, and week number. I am successfully collecting data with the following formula:

=AVG(COLLECT({% Billable}, {Week#}, Jun$5, {Role}, INDEX({Role}, MATCH($[Primary Column]@row, {Role}, 0))))

In cases where that role is not present on the roster, it is showing a #DIVIDEBYZERO error. Is there a way to add into this formula that if there are 0, enter "" in the cell?


Second, I have a sheet that matches the position listed in the primary column with another sheet that contains the max headcount for that position. Here's the formula:

=(INDEX({Max Headcount}, MATCH([Primary Column]@row, {Position}, 0)))

If the max headcount is blank or isn't listed, it returns a #NOMATCH error. Is there a way to add into this formula that if there is no match, enter "" in the cell?

Thanks in advance!

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!