In an opinion piece for the Globe and Mail back in 2018, Roland Paris (an international affairs scholar and former foreign policy adviser to Prime Minister Justin Trudeau) argued that a seat on the UN Security Council would afford Canada positive influence on the world stage. For example:
last time Canada served on the council, in 1999-2000, it led a successful campaign to establish civilian protection as a centrepiece of the UN’s activities and to control the trade in “conflict diamonds,” which were fuelling African wars. Behind the scenes, Canada helped develop new, more effective working methods in the council.
I'd say this piece is worth a read but doesn't address the question of whether and why Norway or Ireland wouldn't achieve similar ends.
Maybe more to the point, a Bloomberg / Time Magazine article explains that this recent bid was politically important for Justin Truedea, as a central part of his foriegn policy strategy.
Trudeau waged a four-year campaign for a council seat in what he hoped would represent a vindication of his foreign policy — a staunch defense of pluralism and multilateralism at a time of global upheaval. [...] The government had seen a return to the security council as a fulfillment of the Canadian leader’s promise — the day after he took power in 2015 — to bring the country “back” on the world stage. “Many of you have worried that Canada has lost its compassionate and constructive voice in the world over the past 10 years,” Trudeau said at the time. “Well, I have a simple message for you: on behalf of 35 million Canadians, we’re back.”
So loss of the bid is certainly a political setback for Trudeau, if not Canada as a whole, or arguably the world if you agree with his particular vision of multilateralism.