VLOOKUP on whole columns in Google Sheets™: faster alternatives

By Paul Kemp · Published 5 October 2026

=VLOOKUP(A2, B:Z, 5, FALSE) is the single most common performance bug in Google Sheets™. The range B:Z covers millions of cells, and an exact-match lookup scans them row by row. One such formula is fine. Hundreds of them, recalculating on every edit, is how a sheet grinds to a halt.

The fixes, easiest first

1. Narrow the range. If the data runs to row 5000, use B1:Z5000. Same answer, far less scanning. This one change usually matters most.
2. Sort and switch to approximate match, but only if you understand the trade-off: TRUE needs sorted data and returns the wrong row silently if it is not sorted. Exact match on a narrow range beats approximate match on a wide one for safety.
3. INDEX/MATCH instead of VLOOKUP. It reads only the two columns involved, and it does not break when columns are inserted.
4. XLOOKUP. Google Sheets™ supports XLOOKUP, which defaults to exact match and takes separate lookup and return ranges, so there is no column counting.
5. FILTER for multiple matches. If you need every match rather than the first, one FILTER beats a column of lookups.

Find every wide lookup

Whole-column ranges hide in old formulas: search for ranges ending at the last columns (B:Z and the like) and full-column references, and narrow each one to the data it actually needs.

Sheet Speed Checker™ grades a Google Sheets™ file and shows where the time goes, including every lookup over an oversized range. Checking the active sheet, the grade, every finding and Undo are free.

Install from the Google Workspace Marketplace™

Google, Google Sheets™ and Google Workspace™ are trademarks of Google LLC. App Garden™ is not affiliated with or endorsed by Google.

Google, Google Sheets™ and Google Workspace™ are trademarks of Google LLC. Sheet Speed Checker is not affiliated with or endorsed by Google.