It depends on what the active ingredient is.
There are a lot of topical pain creams that use menthol, which doesn't actually relieve pain, it just provides a tingling feeling that confuses your brain and distracts you from the pain.
Creams with capsaicin (which is also found in hot peppers) do provide pain relief, but they may irritate the skin, so you should always do a patch test on a small area of skin before widespread use.
There are also topical treatments that contain ibuprofen or aspirin. Not everyone finds them effective, but for the people who do, it can be a good way to avoid the stomach issues that sometimes arise from regularly taking pain relievers orally.
In short, yes. Some topical pain compounds do work. It may take some experimenting to find the one that works best for you.