Fill in the fields and the VLOOKUP formula builds as you type, with each argument explained underneath. Copy it with one click and paste it into Excel.
Your formula appears here, e.g. =VLOOKUP(A2,Products!$A$2:$D$100,3,FALSE)
Nothing is uploaded. The formula is built in your browser as you type.
A VLOOKUP generator writes the formula for you from four answers: what to look for, where to look, which column to bring back, and whether the match must be exact. The usual job is an order sheet with product codes in column A and a price list on a sheet called Products, where each order row needs its unit price. Type A2, Products!A2:D100 and 3, and the generator gives you =VLOOKUP(A2,Products!$A$2:$D$100,3,FALSE).
A2. A number or a piece of text works too, and text gets its quotes added.Products!A2:D100. "Lock the range with $" is ticked by default. Leave it on.E2), and fill down.The formula is built in your browser. Nothing you type is uploaded, and there's no sign-up or usage limit.
The generator takes what you'd type and fixes the parts that go wrong when a formula is written by hand.
| You type | The formula gets | Why |
|---|---|---|
a2 | A2 | Cell references go in as typed, with the column letters capitalised |
42 or TRUE | 42 or TRUE | Numbers and TRUE/FALSE aren't quoted |
Widget | "Widget" | Text needs double quotes inside a formula |
6" pipe | "6"" pipe" | A quote inside text has to be doubled |
"Widget" | "Widget" | Text you already quoted is kept |
=Products | Products | A leading = passes a defined name or expression through as written |
A2:D100 | $A$2:$D$100 | With the lock ticked, every column and row gets a $ |
Products or Sales[Region] in the table array | unchanged | Defined names and table references don't need $ |
Two catches in that table. A bare word in the lookup value field counts as text: Products there becomes "Products", so type =Products if you mean a defined name. And a product code shaped like a cell address, such as XL200, goes in as a reference to cell XL200. Type it with its quotes, "XL200", to look up the text.
The column index is checked while you type. It has to be a whole number of at least 1, and no wider than the range. Type 5 against Products!A2:D100 and the generator stops you with "Products!A2:D100 has 4 columns, so the column index can be at most 4. Excel would return #REF!." A defined name or table doesn't say how wide it is, so any index goes through there.
We checked the output in Excel for Mac against a three-row test table: =VLOOKUP(F1,$A$1:$D$3,3,FALSE) returned the right value from column 3, and the IFERROR version returned Not found for a code that wasn't there.
Without $ signs, the table range travels with the formula. Put =VLOOKUP(A2,Products!A2:D100,3,FALSE) in row 2, fill it to row 50, and row 50 reads =VLOOKUP(A50,Products!A50:D148,3,FALSE). Any code listed in the top 48 rows of the price list is now outside the range and comes back #N/A. The codes further down still work. That mix of right answers and errors is why this one takes a while to spot.
$A$2:$D$100 pins the range. The lookup value needs the opposite: A2 has to stay relative so row 50 looks up A50. The generator therefore adds $ signs to the table array and never to a lookup cell, which goes in as you typed it.
Leave off the fourth argument and VLOOKUP does an approximate match. =VLOOKUP(A2,Products!$A$2:$D$100,3) behaves as if it ended in TRUE. On a price list that isn't sorted smallest to largest, approximate match returns a nearby row with no error, so a wrong price looks exactly like a right one.
The generator always writes the fourth argument, and FALSE is the default. Exact match returns the row whose first column equals the lookup value, or #N/A.
Approximate match is right for bands. With thresholds 0, 50, 60 and 70 in column A and grades in column B, =VLOOKUP(64,$A$2:$B$5,2,TRUE) returns the grade next to 60, the largest threshold not above 64. Tax brackets and commission tiers work the same way. Pick TRUE in the generator and it warns you that the first column must be sorted smallest to largest.
The generator never sees your workbook, so it can't tell you whether a code is in the table. When exact match returns #N/A for a code you can see on screen, the cause is almost always in the data:
ABC-104 with a space after it, pasted from a web export, doesn't equal ABC-104. =LEN(A2) shows the extra character, and TRIM in a helper column removes it. A space from a web page is often a non-breaking one, which TRIM leaves alone: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) handles both.$A$2:$D$100. A whole-column range such as Products!$A:$D, or a table reference, grows with the data.=IFERROR(VLOOKUP(A2,Products!$A$2:$D$100,3,FALSE),"Not found") shows Not found instead of #N/A for a missing code. The catch is that IFERROR swallows every error, not only #N/A. Delete a column from a four-column price list and a formula asking for column 4 now points past the edge of the table. The #REF! error that should tell you so shows as Not found on every row. Get the formula right without IFERROR, then tick the box. The generator shows that warning as soon as you tick the box.
If you only want to catch missing codes, change IFERROR to IFNA after pasting. The generator writes IFERROR only.
A blank fallback becomes "", which looks empty but is text. =E2*F2 on that cell returns #VALUE!. If the result column feeds arithmetic, type 0 as the fallback, and it goes in as a number.
Excel has its own form for this. Formulas > Lookup & Reference > VLOOKUP opens the Function Arguments dialog (the Formula Builder on a Mac) with the same four boxes, and you can drag across the table on the sheet instead of typing its address. It won't add the $ signs, though. Click into the range in the formula bar and press F4 (Cmd+T on a Mac) to make it absolute. That route is better when you'd rather point at the table than type where it is.
On Excel 365 or Excel 2021, XLOOKUP is usually the better function. Exact match is its default, it can return a column to the left of the lookup column, and it takes the return column as a range, so inserting a column doesn't break it: =XLOOKUP(A2,Products!$A$2:$A$100,Products!$C$2:$C$100,"Not found"). VLOOKUP still earns its place in a workbook that has to open in Excel 2019 or older.
"Smith, John", stays.="*"&A2&"*" as the lookup value: the leading = passes it through as written, and it works with exact match.3 points at.The VLOOKUP function page covers each argument in more depth, with worked examples.
FALSE asks VLOOKUP for an exact match, and it returns #N/A when the value isn't in the first column of the table. TRUE, or leaving the fourth argument off, asks for an approximate match instead. That needs the first column sorted smallest to largest, and on unsorted data it returns wrong rows without any error, so use FALSE unless you are looking up bands like grades or tax brackets.
Because the two values only look alike. A trailing space, or a code stored as text on one side and as a number on the other, is enough to make an exact match fail. Also check that the value sits in the first column of the table array, since that is the only column VLOOKUP searches.
The table array is a relative reference, so Excel moves it down one row for every row you fill. Add $ signs, as in $A$2:$D$100, and the range stays put. The lookup cell should stay relative so each row looks up its own value.
Wrap it in IFERROR, for example =IFERROR(VLOOKUP(A2,$A$2:$D$100,3,FALSE),"Not found"). Be aware that IFERROR also hides real mistakes, such as a column index past the edge of the table. IFNA in the same position catches only #N/A and lets every other error show.
Yes. Put the sheet name and an exclamation mark in front of the range, as in Products!$A$2:$D$100, and wrap the sheet name in single quotes if it contains a space: 'Price list'!$A$2:$D$100. One VLOOKUP searches one range, so searching several sheets in turn means one VLOOKUP per sheet, each nested in the previous one's IFERROR.
Use XLOOKUP when everyone who opens the file has Excel 365 or Excel 2021 or later. It defaults to exact match, can return a column to the left of the lookup column, and keeps working when someone inserts a column. Use VLOOKUP when the workbook has to open in Excel 2019 or older, where XLOOKUP shows #NAME?.
Yes, with exact match. An asterisk stands for any run of characters and a question mark for one character, so =VLOOKUP("*"&A2&"*",$A$2:$D$100,3,FALSE) returns the first row whose code contains the text in A2. The generator has no wildcard option, but typing ="*"&A2&"*" as its lookup value works, because anything starting with = goes in as written.