VLOOKUP Formula Generator

Type the word VLOOKUP into a search engine's autocomplete and watch it finish the thought for you: VLOOKUP not working. VLOOKUP another sheet. VLOOKUP returns NA. VLOOKUP exact match. The pattern holds year after year — of all the functions in Excel's library of hundreds, this one keeps the search volume crown, because it is simultaneously the function everyone needs and the function everyone gets burned by. Nothing else comes close. There are formulas people don't know, and then there is VLOOKUP, which people know and still type wrong on Tuesdays.

The scenario is always some version of the same shape: a list of SKUs in one column, a price table on another sheet, and several hundred rows that need the right price brought across. In your head it's one motion — look it up. At the formula bar it's VLOOKUP with four arguments, two of which have famous traps, one of which gets omitted by people who don't know it has a default, and a column index that everyone counts off by one at least once in their career.

This generator is the un-burnable path. Fill in the lookup value, the table range, and the column that has the answer; choose exact or approximate; and out comes a correct VLOOKUP with the fourth argument written out, dollar signs on the range if you'll fill down, and a plain-English recap underneath — "finds SKU-233 in column A of Prices, returns column C, exact match only" — so you can check the formula against the sentence you meant. Copy, paste, done. The haunted autocomplete entries can be someone else's.

The tool asks for the four inputs in the order they matter to you, not the order the function wants them. What are you looking up — a value or a cell reference. Where is the table — a range on this sheet, or a range on another sheet, which the generator writes with the sheet name and dollar signs. Which column holds the answer — a number you pick from a labeled list rather than count on your fingers. And how should it match — exact, or banded approximate for tier tables.

From those fields it emits the formula with the details that separate a working lookup from a fragile one: the fourth argument always written explicitly (FALSE for exact, TRUE for banded), the range anchored with $ signs for fill-down, sheet references qualified properly, and text lookup values quoted while cell references stay bare. Alongside the formula, the recap sentence states the lookup in business language. That sentence is the feature people don't know they wanted until the first time it says "returns column C" and they realize they meant column D.

For shops on modern Excel, the generator also shows the XLOOKUP equivalent of the same inputs, because migrating the two forms in lockstep beats rewriting under deadline pressure. The XLOOKUP Formula Builder on this site is the dedicated version of that side; this page keeps the VLOOKUP flame for the enormous installed base of workbooks where it remains the lingua franca.

1. 1

   Enter the lookup value

   A literal like SKU-233, or a cell reference like A2 for a fill-down column. The generator quotes literals and leaves references bare — the difference that produces #NAME? when hand-done wrong.

2. 2

   Define the table range

   Type it or click it together: A2:C401, or Prices!$A$2:$C$401 for another sheet. A checkbox anchors it with dollar signs so fill-down doesn't stretch the range row by row.

3. 3

   Pick the return column

   Choose from a list showing column letters and headers where given. The generator computes the index — counting the lookup column as 1 — and shows which letter the number lands on, killing the off-by-one before it ships.

4. 4

   Choose the match mode

   Exact for IDs, names, SKUs — anything where close enough is wrong. Banded for sorted tier tables: commission rates, shipping weights, tax-style brackets.

5. 5

   Copy the formula

   The output box holds the finished VLOOKUP with the recap underneath; one click copies it. The IFNA wrapper is an optional toggle for the "not found" text, quoted and closed for you.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Four slots, and each one has a personality. lookup_value is the key you're matching — a literal or, in the overwhelmingly common fill-down case, a relative cell reference that changes per row. table_array is the rectangular range whose first column gets searched; the search column is always the first column, which is the entire reason the function can't look left. col_index_num counts columns from the left edge of that range, with the search column itself as 1 — a numbering scheme that surprises everyone exactly once and then lives in their fingers forever.

range_lookup is the argument with the reputation. FALSE means exact match only, #N/A otherwise. TRUE means approximate: return the row with the largest first-column value less than or equal to the lookup value — a binary-search-shaped behavior that demands an ascending-sorted first column and powers every banded-tier table built since the early 1990s. The trap is the default: omit the argument and Excel behaves as if you typed TRUE. On an unsorted product list, approximate mode doesn't error — it returns the price of whichever SKU happens to sort nearest below the one you asked for. Wrong answer, green checkmark, shipped.

Two behaviors complete the picture. Text matching is case-insensitive, like the = operator, and in exact mode the lookup value can carry wildcards — "SKU-*" matches the first SKU-prefixed entry. And VLOOKUP returns the first match it encounters, top to bottom, which makes it the wrong tool for "the most recent record per customer" without a sort or a timestamp trick — a limitation the XLOOKUP comparison below addresses directly, since search-from-last is a one-argument setting there.

The generator's two match modes exist for genuinely different jobs, so here is one of each, with data small enough to verify by hand. First: exact match, the classic SKU price fill.

Sheet "Prices", range A2:C4:

   A        B             C
1  SKU       Product       Price
2  SKU-041   Brass fitting  8.40
3  SKU-117   Valve seat    21.75
4  SKU-233   Gasket kit    14.20

Sheet "Orders", column A holds SKUs to price.

The banded example repays study, because it's the one place approximate match is not a trap but the feature itself — and its rules are precise. Equal-to-a-floor lands in that band. Below every floor is an error. And the whole mechanism assumes ascending order in the first column: one out-of-order row and the results above it become fiction. This is the same logic that powers tax-bracket tables and shipping-weight charts, and it is the reason the tier table, not the nested IF, is how experienced spreadsheet builders encode any thresholds that a business owner might change. The IF Formula Builder covers the branching alternative when the rule is too irregular for bands.

• #N/A on visible data. Trailing spaces (invisible in cells, visible in the formula bar), text-stored numbers on either side, and nonbreaking spaces pasted from web pages — the three horsemen. TRIM and CLEAN on the key column fix the first and third; multiply-by-1 paste special or VALUE() fixes the second.
• The silent column shift. Insert a column inside the table and col_index_num still points at position 3, which is now a different column. The formula recalculates cleanly and returns the wrong field. If your workbook screams at inserters, this is why.
• The unanchored fill-down. =VLOOKUP(A2, B2:C400, 2, FALSE) filled down shifts to B3:C401, B4:C402... and rows near the bottom start matching against a table that no longer contains their keys. $B$2:$C$400 — or the generator's anchor checkbox — is the fix.
• #REF! after deleting a column. The table shrinks below the index you asked for, and VLOOKUP says so the only way it can. Recover with undo; the formula won't self-heal.
• Duplicate keys return the first match. For a price list that's fine; for a transaction log where a customer appears repeatedly, it's wrong by design — the lookup needs a compound key (a helper column concatenating customer and date) or a different tool entirely.
• Case-sensitivity illusions. Because matching ignores case, ab-1 and AB-1 are the same key to VLOOKUP. If they're distinct products in your catalog, exact mode still won't separate them — build a key column through EXACT-compatible means instead of relying on the lookup.
• Approximate mode's sorting assumption. It's not "close is fine" — it's "largest value at or below, assuming ascending order." On unsorted data the behavior isn't random but is effectively unpredictable to a reader, which is worse. Exact mode or a properly sorted tier table; nothing in between.
• Whole-column ranges on huge sheets. VLOOKUP over A:B on a million-row sheet is optimized to the used range but still slower than a bounded range, and a workbook with thousands of such lookups recalculates at a pace people notice. Bound your ranges; your users' patience is a shared resource.

Procurement and inventory teams are the heartland: purchase orders with SKUs to price against vendor sheets, warehouse counts against item masters, catalog updates against last season's file. The work is periodic, high-volume, and exactly fill-down-shaped — one generated formula, several hundred rows, a deadline at four. The generator's anchored-range default exists because of this group specifically; the unanchored fill-down is their most common rework.

HR and finance run the same pattern on people and accounts: employee IDs against a directory, account codes against a chart of accounts, cost centers against department names. Their twist is privacy — the lookup table is often the sensitive artifact, and a tool that never touches the workbook, just emits a formula from typed references, is the right shape for data that shouldn't tour through third-party services. Admins assembling student gradebooks and rosters make up the third wave, arriving every September with the same energy and the same off-by-one.

And there's the permanent student population: VLOOKUP is the second formula taught in nearly every Excel course, right after SUM, and the homework due tomorrow is the same one the forums have answered since the 1990s. For them the recap line doubles as a tutor — the formula and the sentence it means, side by side, until the syntax stops being foreign.

The successor conversation is legitimate and worth having precisely, because the tradeoffs are real and version availability still decides plenty of shops.

Our recommendation, stated plainly: build new lookups with XLOOKUP if every consumer of the workbook is on Excel 2021, Microsoft 365, or current Google Sheets. Stay with VLOOKUP when the file travels — to a client on Excel 2016, a plant machine on 2013, a regulator who opens anything — because #NAME? in a recipient's Excel is a worse failure than any VLOOKUP quirk. INDEX/MATCH remains the correct answer in workbooks that already speak it and in the leftward-lookup cases on old versions. The generator emits VLOOKUP and shows the XLOOKUP twin, which is exactly how migrations should happen: side by side, workbook by workbook, never in one heroic Friday-night rewrite.

The generator never opens a workbook, so there's nothing to upload and nothing to leak. The fields hold references and literals you type — A2, Prices!$A$2:$C$4, SKU-233 — and the formula is assembled locally in your browser. No account, no saved history, no server-side assembly. The tool works offline after the page loads, and closing the tab ends its memory of you entirely.

That design is deliberate for lookup work specifically. The values people look up — salaries keyed by employee ID, account balances by account number, prices by confidential SKU — describe the business's nervous system. A generator that required handing over the table to explain a formula would be a bad trade. This one asks for the shape of your data, not the data, and the shape was never secret to begin with.

The lookup family on this site covers the whole journey: the XLOOKUP Formula Builder for modern-Excel work and the migration path, the SUMIF/COUNTIF Formula Builder when the question is aggregation rather than retrieval, and the Excel Formula Formatter for printing a finished lookup in review-ready shape before it goes into a shared model. Pasting a colleague's VLOOKUP to understand it first? That's the Excel Formula Explainer, one cluster over.