Looking for a cost effective product(between walls) to reduce noise.

Are you looking to reduce things like tv/music/vocal volume from the neighbors or something more?

Not sure how far you want to go but rockwool and two layers of sheetrock will do a lot for you.

Combo of loud voices and music from time to time.

The neighbor uses his townhouse as an ABNB so there is 4-6 people in a 1200 square foot space on a weekend most of the time.
Need to do most of the wall between the bedrooms and living area.

A few ideas have been listed already, the only thing that stops sound is mass and isolaiton. Use Rockwool for insulation between the walls(Rockwool will work better than sprayfoam for sound deadening) Use Isolation clips on the drywall connection to the studs. Seal every hole you can find and use sound dampening pads on all outlet and switch boxes. If you can afford the money and space, you could look at building a double wall with a several inch air gap between the walls, insulating both walls and double up on the drywall using a product called "Green Glue" sandwiched between the drywall sheets. Anything that works well will be expensive, especially on an existing space. Less expensive options would be heavy, dense curtains. Sound deadening blankets etc. Don't buy acoustic foam panels thinking that they will do a lot to block the sound, that is not their purpose. Acoustic panels are used improve the sound quality within a room, not necessarily block sound

I would nail 2”X2” furring strips on the common wall and fill the space between furring with 2” rigid foam board. Caulk all gaps and cover the whole thing over with drywall. Pay particular attention to spaces above ceilings and also junction boxes/switch boxes in the wall

I did this on my old office and it worked very well.

Hang a decorative quilt on the wall.