Added new light to circuit but it turns on when switch is off
What you did was to place the new light in parallel with the switch, instead of in parallel with the existing lights. With the switch on, the new light is shorted out and and the old lights get power. With the switch off, you have the new light in series with the old lights. I’ll bet the the new light is very low wattage (e.g. LED) while the old lights are high wattage (incandescent). The low wattage light gets almost all of the voltage and the high wattage light gets so little that it doesn’t visibly glow.
As @isherwood noted, what you have in the box is a switch loop (actually two, but that’s beside the point). What you need to connect to is the switched hot and neutral and the only place you’ll probably find those is at one of the existing lights.
Edit to add more details:
Here's the wiring called a switch loop:

The power enters via the black (hot) wire into the fixture box. It continues into the switch box via the white (with black tape) wire to the switch. When the switch is turned on, the power continues back up the switched hot (black wire) to the light. After the light, the power returns to the source on the white wire (neutral).
Depending on the age of the installation, there may or may not be an additional white wire carrying the neutral into the switch box. This is to allow the use of smart switches which require a neutral but in your case, would not be connected in the switch box, but just capped off.
The power could also enter via the switch box, in which case, it would look something like this:

The difference here is that the hot directly enters the switch box and goes to the switch. The switched hot continues up to the light. After the light, the power returns on the neutral to the switch box where it's spliced to the neutral back to the source.

You need to connect the new light between the switched hot and the neutral. This is the only way it will work correctly. Here's how to tell where that is.
If there were no extra white wires in the switch box or there were just one or two (one from each existing light) not connected, then you have a switch loop: you cannot connect the new light at the switch. Instead, you must connect to one of the existing lights, back to the black on the light and white to the white on the light.
On the other hand, if there where two sets of white wires, each connecting through (spliced with a wirenut or such), then the power is first arriving at the switch and you can connect there. Connect your new black to the side of the switch going to the lights (if you can't tell, try one. If the switch won't control the light, switch it to the other). Your new white connects to the spliced whites in the switch box: remove the wire nut, add the new wire and re-attach it. You may need a larger wire nut but just going from 2 to 3 wires, probably not.
Note: I have ignored details such as how to connect a wirenut and have ignored the grounds. You must properly connect the grounds assuming there are ones there (maybe not on very old installations).
Hopefully, this is enough to help you. If you are still lost, I'm afraid you need to call a professional: an electrician or, for this, maybe a handyman. Please don't experiment. House wiring voltages or currents make it just to easy to kill yourself or someone else or to cause a fire! Be safe!