XLOOKUP looks up a value and returns a result from a separate array. Unlike VLOOKUP it can look left, returns exact matches by default, and takes an optional "if not found" argument.
Do it with AI insteadEnter the lookup value
Type =XLOOKUP( and select the value you want to find.
Select lookup and return arrays
Pick the column to search, then the column to return — they can be in any order.
Add an if-not-found message
Add a 4th argument like "Not found" to avoid #N/A.
=XLOOKUP(A2, Sheet2!B:B, Sheet2!D:D, "Not found")Usually yes — it looks both directions, defaults to exact match, and handles not-found cleanly. It needs Excel 2021 or Microsoft 365.