I'm far from an expert on tariffs but they are a tax and they do affect the prices we will pay for goods. Bob Murphy does a good job explaining them back when Trump was in office.
I agree and Bob has probably addressed why some of these theoretical results are implausible.
In brief, our fancy trade models indicate that a very large economy, trading with smaller ones, can more than offset the economic loss of higher prices with the additional economic activity from producing those goods domestically. The trade partners will all be worse off to the point where the world as a whole is less prosperous, though.
Tariffs don't always affect equilibrium prices very much, because domestic production is often nearly as efficient. Also, a large market like the US can usually pass most of the tax incidence onto it's trade partners.
Like I said, they're bad for humanity and it's immoral to prevent free association, but it's possible for certain countries to enrich themselves at their neighbors' expense via certain tariffs.
reply