What happens when the centripetal force is equal and opposite to the centrifugal force?
First, it must be stated that Newton's laws only hold in inertial frames. What this means is that accelerations must arise from forces (Second Law), and these forces arise from interactions (Third Law). The issue with rotating frames is that accelerations arise when forces of interactions are not present, so Newton's laws do not hold in rotating frames.
However, the second law ($\mathbf F=m\mathbf a$) is nice to use since it tells us how to determine the position and velocity of a body given initial conditions. Therefore, we define "fictitious" centrifugal and Coriolis forces in order to keep this framework. They are "fictitious" because they are an artifact of the rotating reference frame rather than interactions, but they are not fake (for example, they are very real for anyone going around a sharp turn in a car). Essentially we have opted to abandon the third law in order to keep the second law.
Now, onto your specific inquiry: If you are in a rotating frame, and there is a force equally opposing the centrifugal force, then yes the net force is zero (assuming no Coriolis force either). Therefore in the rotating frame there is no acceleration of the object in question.
Of course, if you looked at the scenario from an inertial frame you would have a non-zero acceleration of the object as there is now a non-zero net force that is the centripetal force.
The centrifugal force is a "fictitious force" that appears when working in a rotating coordinate system. Basically (together with the Coriolis force) it's the imaginary force that would, if real, make objects move with respect to a fixed, non-rotating coordinate system in the same way that they actually move (due to inertia) with respect to the rotating coordinates that we're using.
If all that sounds really confusing, please see the second half of this earlier answer I wrote, where I try to explain this in more detail (and with pictures!).
Anyway, the point of these fictitious forces is that they let us apply the same laws and formulas of Newtonian physics in a rotating frame of reference as we would in a non-rotating one, and still get physically correct results, as long as we remember to also include the effect of those imaginary forces on all objects.
For example, in a normal non-rotating coordinate system, a stationary object will remain stationary if (and only if) all the forces acting upon it cancel out, so that the net force acting on the object is zero. In a rotating coordinate system, an object which is stationary with respect to the coordinates (i.e. rotating along with them around the same axis at the same speed) will remain so if (and only if) all the real and imaginary forces acting on it cancel out, leaving an (apparent) net force of zero.
Here's a simple example, taken from the answer I linked above. Imagine two spheres floating in space near each other. If you do nothing, they'll just keep floating there. If you push each of them in different directions, then they'll each float in the direction you pushed them, away from each other. But if the spheres have been tied together with a string, then the tension of the string will exert a centripetal force that will curve their trajectories into circles:
Now, if we look at the same system of two spheres and a string in a coordinate system which is rotating along with them, then the spheres will look as it they were motionless. But clearly something is still pulling the string taut (and, if it's elastic, stretching it), counteracting the tension force that is pulling the spheres together. We call this apparent force (which is really just inertia, hidden by the fact that our coordinate system is rotating) the "centrifugal force":
In this example, since the spheres are motionless with respect to the rotating coordinate system, and since the centripetal and centrifugal forces balance out, they will remain motionless with respect to the rotating coordinates — i.e. they will continue to rotate around the same axis at the same speed.