The Lookup Formula Cheat Sheet: VLOOKUP, INDEX-MATCH, and XLOOKUP for People Who Just Need the Answer

Stop Googling VLOOKUP every time. A practical lookup formula guide for Google Sheets — VLOOKUP, INDEX-MATCH, and XLOOKUP explained with real examples.

I have written the same VLOOKUP formula probably four hundred times, and I still Google the syntax about once a month. That's not a confession — that's just how lookup formulas work for people who use spreadsheets to run their business, not to impress a hiring manager. You know you need to pull a price from one tab into another, or match a client name to their invoice total, or grab a status from a master list. You know a formula does this. You just can't remember which one, or in what order the arguments go, or why it's returning #N/A when the data is RIGHT THERE.

This is the post I wish I'd had bookmarked three years ago. Not a computer science lecture. Not a 45-minute YouTube video. Just the three lookup formulas that matter in Google Sheets — VLOOKUP, INDEX-MATCH, and XLOOKUP — explained the way you'd explain them to a coworker who's standing behind your chair asking for help.

Why You Keep Forgetting: The Real Problem With Lookup Formulas

Here's the thing nobody says out loud: lookup formulas are conceptually simple but syntactically annoying. The idea is always the same — "I have a value in Column A, go find the matching row, and bring me back the value from Column D." That's it. Every lookup formula is doing some version of that one task. The reason you keep Googling it is that each formula packages those instructions in a slightly different order, with slightly different quirks, and the error messages tell you nothing useful.

So before we get into syntax, let's agree on the mental model. Every lookup has three parts: (1) What are you looking for? (2) Where should the formula look? (3) What should it bring back? If you can answer those three questions while looking at your spreadsheet, you can write any lookup formula. The rest is just remembering where the commas go.

VLOOKUP: The One You Already Half-Know

VLOOKUP is the formula most people learn first, usually under duress. It's been around forever, it works, and it has one absolutely maddening limitation that we'll get to. But for probably 60% of lookup tasks, it's the fastest to write.

The syntax:

In human language: search_key is the value you're looking for (a client name, an invoice number, a product SKU). range is the block of cells to search — and this is important — the leftmost column of this range must contain the values you're searching through. index is the column number (counting from the left of your range) that contains the answer you want. is_sorted should almost always be FALSE (or 0) unless you're doing something unusual. FALSE means "find me an exact match," which is what you want 99% of the time.

Real example: you have a Products tab with SKUs in column A and prices in column D. On your Invoice tab, you type a SKU and want the price to auto-fill.

That says: take the value in A2, search the first column of Products!A:D for a match, and bring me back the value from column 4 of that range. Easy.

The other common gotcha: that index number. If you insert a column into your source data, your VLOOKUP breaks because the column number shifted. You won't get an error — you'll get the wrong answer, which is worse.

INDEX-MATCH: The Upgrade That's Worth Learning

INDEX-MATCH looks intimidating the first time you see it. Two functions nested together? Feels like a lot. But once it clicks, you'll probably use it more than VLOOKUP, because it doesn't have the "can only look right" limitation, and it doesn't break when you insert columns.

Here's the mental model: MATCH finds the row number where your value lives. INDEX goes to a specific column and grabs the value from that row. They're a two-step team.

return_range is the column that has the answer you want. search_key is the value you're looking for. lookup_range is the column that contains the values you're searching through. The 0 at the end means exact match (same idea as FALSE in VLOOKUP).

Same example as before — SKU in A2 on your Invoice tab, prices in column D of Products, SKUs in column A of Products:

Notice something? You're pointing directly at column D for the return and column A for the search. It doesn't matter which one is to the left or right. If your layout changes and you need to search column C and return column A, you just swap the references. No restructuring your data. No hair-pulling.

XLOOKUP: The One Google Sheets Finally Added

XLOOKUP landed in Google Sheets in 2023, and it's essentially Google saying, "Yeah, VLOOKUP has some problems. Here's the fix." It combines the simplicity of VLOOKUP with the flexibility of INDEX-MATCH, and it adds a couple of tricks neither of them can do.

The first three arguments are the ones you'll use 90% of the time: what you're looking for, where to look, and what to bring back. Same SKU-to-price example:

That's it. No column numbers to count. No FALSE to remember. No left-right restriction. It just reads like a sentence: look up A2 in the Products SKU column, return the matching value from the Products price column.

The optional fourth argument — not_found — is genuinely useful. Instead of getting an ugly #N/A error when there's no match, you can specify what shows up instead:

Now if someone types a SKU that doesn't exist, the cell says "Not in catalog" instead of screaming #N/A at them. Small thing. Makes a huge difference when you're sharing the sheet with someone who doesn't speak formula.

The Comparison Table You'll Actually Bookmark

I keep a version of this taped inside my notebook (yes, on paper — fight me). Here's the quick decision framework:

• Use VLOOKUP when: your lookup column is the leftmost column, you want something quick and readable, and you're not worried about columns shifting.
• Use INDEX-MATCH when: you need to look left, your data set is large (5,000+ rows), or your sheet structure might change and you don't want formulas breaking silently.
• Use XLOOKUP when: you want the cleanest syntax, you need a custom error message for missing values, or you need to search bottom-to-top. (It does everything VLOOKUP does, better.)
• All three return the first match they find. If you have duplicate values in your lookup column, you'll get the first one — not all of them. For multiple matches, you'd need FILTER (a topic for another day).
• All three can reference other tabs in the same spreadsheet. None of them can reach into a different Google Sheets file without IMPORTRANGE.

If you're starting from scratch today and don't have years of VLOOKUP muscle memory, just learn XLOOKUP. It handles every scenario VLOOKUP handles, plus the ones VLOOKUP can't.

Putting Lookups to Work: Real Patterns That Save Hours

Knowing the syntax is step one. Knowing where to deploy lookups is where they actually start saving you time. Here are four patterns I use constantly.

Pattern 1: Auto-fill client details on an invoice. You have a Clients tab with names, emails, addresses, phone numbers. On your invoice tab, you type or select a client name, and XLOOKUP fills in everything else. One lookup per field, all pointing at the same Clients tab. Five minutes to set up, saves you from copy-pasting forever.

Pattern 2: Status dashboard that pulls from a project tracker. Your project tracker has 80 rows with a status column. Your dashboard tab uses COUNTIF for the summary numbers, but for the "show me the details of this specific project" section, an XLOOKUP on the project name pulls the status, deadline, budget, and owner into a clean read-only view.

Pattern 3: Price lookups on order forms. You maintain a product catalog on one tab. Your order form has a SKU or product name column. A lookup auto-fills the unit price, and a simple multiplication formula handles the line total. This is the backbone of every line sheet, estimate template, and invoice I've ever built.

Pattern 4: Cross-referencing two data sources. You exported a CSV from your CRM and pasted it into one tab. Your internal tracking lives on another tab. An INDEX-MATCH or XLOOKUP on email address (the most reliable unique identifier) lets you pull CRM data into your tracker without manually matching rows. I do this at least twice a month.

The Five Mistakes That Cause 90% of Lookup Errors

When a lookup formula breaks, the error message is almost never helpful. Here's what's actually going wrong, in order of how often I see it:

• Trailing spaces. Your lookup value is "ABC-100" but the source data has "ABC-100 " with an invisible space. Fix it with TRIM: =XLOOKUP(TRIM(A2), ...) or clean your source data with Find & Replace.
• Mismatched data types. The lookup column has the number 1001 stored as text, but your search key is the number 1001 (or vice versa). Google Sheets treats these as different values. Wrap your search key in VALUE() or TEXT() to force a match.
• Wrong range in VLOOKUP. The lookup column isn't the leftmost column of your range. This doesn't throw an error — it just returns wrong results. Switch to XLOOKUP or INDEX-MATCH.
• Forgetting FALSE / 0 for exact match. If you omit the last argument in VLOOKUP or use 1 instead of 0 in MATCH, the formula assumes your data is sorted and does an approximate match. The results look plausible but are silently wrong.
• Referencing the wrong tab. You copied the formula from one sheet to another and the tab reference didn't update. Double-check that Products!A:A is still pointing where you think it is.

Combining Lookups With Data Validation for a Bulletproof Sheet

The best lookup formulas in the world still break if someone types "Jhon" instead of "John" in the search cell. The fix isn't a better formula — it's constraining the input. Set up a dropdown using Data Validation that pulls from the same list your lookup searches. Now the user picks from valid options, and the lookup always finds a match.

Here's my standard setup: the source list lives on a dedicated tab (I usually call it "Ref" or "Lists"). Data validation on the input cell uses a range reference to that list. The lookup formula points to the same tab. Everything stays in sync because everything draws from one place. If you add a new client to the Ref tab, the dropdown updates and the lookup can find them. Zero maintenance.

If you want the full walkthrough on setting up data validation, we wrote a dedicated post on that — it covers dropdowns, custom rules, and error messages in about 20 minutes of reading.

What to Do Right Now

You don't need to memorize all three formulas today. Here's what I'd actually do: open the messiest spreadsheet you're currently working in and find one place where you're manually looking something up — scanning a column, copying a value from one tab to another, or typing something you know exists somewhere else in the sheet. Write one XLOOKUP to automate that. Just one.

Once that formula works, you'll see two more places to use it. That's how it always goes. And the next time you forget the syntax (you will, we all do), this post will still be here.

Frequently Asked Questions

What's the difference between VLOOKUP and XLOOKUP in Google Sheets?

XLOOKUP is the modern replacement for VLOOKUP. It can search left or right (VLOOKUP can only search right), returns exact matches by default (no need to remember FALSE), and lets you set a custom value when no match is found instead of showing #N/A. If you're starting fresh, use XLOOKUP.

Why does my VLOOKUP return #N/A when the value exists?

The most common causes are trailing spaces in your data (fix with TRIM), mismatched data types (one cell stores a number as text, the other as an actual number), or forgetting to set the last argument to FALSE for an exact match. Isolate the problem by checking the lookup value and source data manually in a filtered view.

Can I use VLOOKUP or XLOOKUP across different Google Sheets files?

Not directly. Lookup formulas only work within a single spreadsheet file. To pull data from a different Google Sheets file, you'd first need to use IMPORTRANGE to bring that data into a tab in your current spreadsheet, then run your lookup against the imported data.

Is INDEX-MATCH faster than VLOOKUP in Google Sheets?

On large data sets (5,000+ rows), INDEX-MATCH is generally faster because MATCH only searches a single column, while VLOOKUP loads the entire range you specify. On small sheets, the speed difference is negligible and you should use whichever formula you find easier to read and maintain.

How do I do a lookup that returns multiple results in Google Sheets?

VLOOKUP, INDEX-MATCH, and XLOOKUP all return only the first match. For multiple matching rows, use the FILTER function instead: =FILTER(return_range, criteria_range=search_value). FILTER returns every row that matches your criteria, not just the first one.