Index Match Formula Errors

Nick Allgauer

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!

Leibel S

@Nick Allgauer

Wrap your functions in IFERROR functions.

Leibel S

@Nick Allgauer

Wrap your functions in IFERROR functions.