XLOOKUP return range keeps shifting whenever someone adds a column looking for a more stable approach

this has bitten us three times now and we're tired of fixing it.

we have an XLOOKUP pulling from a shared source sheet that about 6 people edit. works fine until someone adds a column, then the return range shifts and everything breaks quietly — no error, just wrong data flowing into the dashboard. somehow that's worse.

current formula is basically:

=XLOOKUP(A2, Sheet2!B:B: B:B, Sheet2!E:E: E:E, "Not Found")

we've looked into named ranges but maintaining them across 8 sheets with people who have different Excel versions (mostly 365, one on 2019) feels like it'll create more problems than it solves.

also tried INDEX/MATCH with a MATCH on the header row so it finds the column by name dynamically — worked, but the formula got complicated enough that nobody on the team wants to touch it.

is there a cleaner pattern that actually holds up in a real shared workbook? or is named ranges genuinely the right call and we just need to commit to it?