Did Brody have Alzheimer's or some other neurological condition?
I'm considering an in-universe explanation for Brody's personality change between Raiders and Last Crusade. He seems to have gone from a fairly savvy, competent academic and possible former adventurer himself - he even says he would have gone after the Ark himself if he were a little younger - to a bumbling comic relief sidekick who, as noted, once got lost in his own museum.
I understand that the tone of the two movies is quite different, and Brody's change serves that new tone. In universe, though, I like to think that between movies he suffered some kind of health downturn that his friends recognise but haven't said anything about.
There's also the possibility that Brody was always like that and he simply doesn't have the opportunity to 'bumble' in Raiders, and that Indy simply humours him when he says things about how he could go after artifacts himself. I don't buy it though - the way he talks about the Ark makes me think that Raiders Brody has Seen A Few Things.