Why does VLOOKUP return #N/A when the value is clearly there?

Almost always a data-type mismatch or hidden spaces: the lookup column stores numbers as text, so 1001 never matches 1001. Look for the green text-format triangles, convert via Data, then Text to Columns, run TRIM on the column to strip stray spaces, and set the fourth argument to FALSE for exact matching. Converting the column once fixes the entire sheet.

Is XLOOKUP better than VLOOKUP?

For modern Excel, yes: XLOOKUP searches any direction, defaults to exact matching, and takes a built-in if-not-found value instead of an IFERROR wrapper. VLOOKUP remains fine for quick lookups to the right of the key, and on huge sorted files an approximate-match VLOOKUP can outperform everything – sort the data and use TRUE as the fourth argument.

How do I stop Excel freezing on large files?

Replace whole-column references with actual ranges or tables, swap volatile functions such as OFFSET and INDIRECT for INDEX-based alternatives, consolidate conditional formatting rules over used ranges only, and delete phantom used ranges (Ctrl+End to find them). Setting calculation to Manual with F9 on demand keeps you working while a heavy file recalculates.

How do I keep the header row visible while scrolling?

Click any cell below the header row, choose View, then Freeze Panes, then Freeze Panes; for the simple case, Freeze Top Row does it in one click. The keyboard route is Alt+W, F, F, and the same menu unfreezes whenever the layout changes.

What is the fastest way to split full names into first and last columns?

Type the first result manually – Jane for Jane Smith – then press Ctrl+E on the next cell or choose Data, then Flash Fill. Excel copies the pattern down the column instantly. Remember it writes static values, so re-run Flash Fill if the source names change later.

How to Fix VLOOKUP in Excel: 20 Tips and Shortcuts (2026)