Yikes, what a hot topic![:0]
I don't have the courage to claim to know what the designers had in mind for any particular vehicle, so this is kind of a tough Q to answer...but the poor ol' Japanese Type 97 Chi-ha sure had a time of it against Shermans and Lees and even Stuarts in the Pacific. Everything I've read about the Chi-ha in Japanese praises the the courage of the hapless crews who threw their undergunned, underarmored steeds headlong towards certain destruction against the vastly superior Allied armor.
So, against other armor, the Type 97 was entirely unsuccessful.
In the infantry support role against forces lacking armor, however, it was effective.
Now, what exactly the designers had in mind for the the li'l Chi-ha, I don't know!
In principle, however, since the axis powers lost the war, I suppose you could name any axis vehicle as "least successful!"
They were all designed to win a war, which, of course, they didn't.
I know, I know, it wasn't the TANK'S fault!!