VLOOKUP has the reputation of being hard because tutorials explain it with theory. It's actually one sentence: go find this value over there and tell me what's next to it.
Five minutes with one concrete example and you'll use it forever, in work, budgets, inventories, everything.
The fast version
- Set up a tiny practice table. In A1 write Code, B1 Price. Then three rows: A100 with 25.99, A200 with 9.50, A300 with 4.25. That's your 'database'.
- Understand the formula parts. The formula is =VLOOKUP(search_value, table_range, column_number, exact_match). In plain words: look for this, inside this table, give me the value from column N, and don't guess.
- Write your first lookup. In D1 type the code A200. In E1 type =VLOOKUP(D1, A:B, 2, FALSE). The result: 9.50. Excel found A200 in column A and returned whatever sits in the 2nd column of the range.
The thorough version
- Always use FALSE (almost always). That last argument set to FALSE means exact match only, which is what you want 99% of the time. TRUE lets Excel 'find the nearest', a source of legendary and silent errors.
- Memorize the pattern: search value, table, column number, FALSE
- The search column must always be the LEFTmost of the range
- Fix the #N/A when it appears. #N/A means 'not found': typo in the search value, extra spaces, or the code truly isn't in column A. TRIM() wrapped around values cleans invisible spaces, the sneakiest cause.
- Lock the table with dollars before copying. When copying the formula down many rows, use =VLOOKUP(D1, $A$1:$B$100, 2, FALSE). The dollar signs keep the table range fixed while the search value moves.
What trips people up
- Sorting the table AFTER writing formulas with column numbers can silently return wrong data. Dollar locks plus not moving columns keeps you safe.
- Numbers stored as text don't match real numbers. If a lookup of a code fails while it's visibly there, that's the reason.
A few things worth knowing
- XLOOKUP (newer Excel and Google Sheets) does the same job better and reads both directions: worth learning right after this one.
- Column numbers count only inside your range: if the table starts at column C, column D is '2'.
- Test with a value you know exists the first time. Confidence first, scale later.
Quick answers
Is VLOOKUP obsolete because of XLOOKUP?
It still runs in every spreadsheet on Earth and millions of files rely on it. Learn both; VLOOKUP explained everything else.
Google Sheets formula identical?
Character for character, yes.
Keep reading
- How to Make a Simple Monthly Budget in Google Sheets (No Template Needed)
- How to Make Windows 11 Look and Feel Like Windows 10
- Motorcycle Tire Pressure: How to Check It Properly (and Why It Changes Everything)
One last thing
One pattern, five ingredients, and spreadsheets start fetching data for you instead of the other way around.
