XLOOKUP

XLOOKUP, introduced in Microsoft 365, is the successor to VLOOKUP and HLOOKUP. You give it a lookup value, a lookup array, and a return array, and it finds the match and returns the corresponding value. Crucially it can look in any direction, return columns to the left of the search, and it defaults to exact match, removing VLOOKUP's most dangerous default.

It also survives column insertions (because you reference arrays, not a fixed column number) and has a built-in 'if not found' argument so you can return a clean message instead of #N/A. The catch is availability: XLOOKUP only exists in Microsoft 365 and Excel 2021+, so workbooks shared with older Excel will show errors. For one-off comparison of two lists it is excellent; for full file diffs, a purpose-built comparison still scales better.