powders will show one load better than others. As to a sweet spot it get subjective. No, not all powders work well in a given gun. With rifles, powders that full the case better give better result. If the powder is too slow you don't get a complete burn within the barrel. this will result in greater variance in velocity thus effect accuracy. I have noted that a given barrel tends to work best within a narrow velocity range. in the end your looking for the best powder to give you that velocity. I can only conclude it has to do with barrel harmonics.
if you can't get good accuracy with a given bullet/ powder combination you can either change powders OR change bullets. Don't do both at once. Change one variable at a time. I choose to change powders first as I have selected a bullet I want to use. For my rifles I have found that Sierra bullets work best. I can't afford those custom bullets. With my 22-250 the times I have tried Hornaday bullets I have found a large variation in bullet weight. It could be that I just got bad batches of bullets as some people favor them. Nosler's didn't work any better than Sierra's and cost more.
As always YMMV
With pistols, Shooting at 25 yards a 1 MOA group is only .25"