VLOOKUP searches for a value in the first column of a table and returns a matching value from another column in the same row. The formula is =VLOOKUP(lookup value, table, column number, TRUE or FALSE), and those four pieces are its four arguments. Once you can name what each one does, the function stops feeling mysterious. This guide walks through one worked example and the errors you are most likely to hit.
What VLOOKUP does (and why it is worth learning)
VLOOKUP stands for “vertical lookup.” It searches down a column for a value, then pulls back a related piece of information from the same row. If you have a list of customer IDs in one place and their email addresses in another, VLOOKUP can fetch each email using the ID as the link between the two.
That link is the key idea. Your lookup works best when both data sets share a unique identifier, such as a product code, a SKU or a customer ID, that appears only once per record. If the identifier repeats, VLOOKUP uses the first match it finds, which may not be the row you wanted.
One rule to remember before we start: VLOOKUP only looks to the right. The value you search for must sit in the first (leftmost) column of your table, and the answer you want must be in a column to its right.
The four arguments, explained
Here is the syntax as Microsoft documents it, with plain-English notes on each argument.
| Argument | What it means | Notes |
|---|---|---|
| 1. Lookup value | The thing you are searching for | Can be a typed value or a cell reference. Text typed directly into the formula needs double quotes. |
| 2. Table array | The block of cells to search | Two or more columns. The lookup value is searched for in the first column of this range. |
| 3. Column index number | Which column of the table to return a value from | Counted from the left of your table: the first column is 1, the second is 2, and so on. |
| 4. Range lookup | Exact or approximate match | FALSE finds an exact match. TRUE finds an approximate match. If you leave it out, TRUE is the default. |
The fourth argument trips up more beginners than any other, so we will come back to it below.
A worked example, step by step
Imagine a small product table in cells A1 to D6. Column A holds product IDs, column B the product name, column C the price and column D the quantity. You want to type an ID into a cell and get the product name back.
The classic worked example from Tech on the Net uses exactly this layout. With the ID 10251 in the first column, these formulas return different columns from the same row:
- =VLOOKUP(10251, A1:B6, 2, FALSE) returns “Pears” (the 2nd column).
- =VLOOKUP(10251, A1:C6, 3, FALSE) returns $18.60 (the 3rd column).
- =VLOOKUP(10251, A1:D6, 4, FALSE) returns 9 (the 4th column).
Notice what changes between them: only the table range and the column number. The lookup value and the match type stay the same. Here is how to build your own, one argument at a time:
- Click the cell where you want the answer and type =VLOOKUP(
- Add the lookup value. Click the cell that holds the ID you want to find (or type the ID).
- Add the table. Select the whole block, making sure the ID column is the first column you include.
- Add the column number. Count from the left of your selected block to the column that holds the answer.
- Add FALSE for an exact match, close the bracket and press Enter.
Tip: when you copy the formula down a list, you will usually want the table range locked in place with dollar signs (for example $A$1:$D$6) so it does not shift as you drag. Check Microsoft’s official function page for the current guidance on absolute references.
Exact match (FALSE) vs approximate match (TRUE)
The last argument decides how strict the search is. This is where many wrong answers come from, because TRUE is the default when you leave the argument out.
- FALSE (exact match): VLOOKUP only returns a result if it finds the exact value. If it does not, you get #N/A. The first column does not need to be sorted.
- TRUE (approximate match): If there is no exact match, VLOOKUP returns the next smaller value. For this to work properly, the first column of your table must be sorted in ascending order, or you may get an incorrect result.
The Tech on the Net example shows the difference clearly. Searching for 10248, which is not in the list, returns #N/A with FALSE but returns “Apples” with TRUE, because Excel falls back to the next smaller value. For things like IDs, names and SKUs, use FALSE almost every time. Approximate match is meant for situations like looking up which band a number falls into.
Common VLOOKUP errors and how to fix them
| Problem | Likely cause | Fix |
|---|---|---|
| #N/A | With FALSE, no exact match was found. With TRUE, the lookup value is smaller than the smallest value in the first column. | Check the spelling and the ID, then confirm the value really exists in the first column of your table. |
| #VALUE! | The column index number is less than 1. | Use 1 or higher. |
| #REF! | The column index number is larger than the number of columns in your table. | Widen the table range or lower the column number. |
| Wrong or unexpected result | Hidden spaces, mixed straight and curly quotation marks, or nonprinting characters in the lookup column. Numbers or dates stored as text can cause the same problem. | Clean the data (Excel has TRIM and CLEAN functions) and make sure numbers are stored as numbers. |
| Result is from the “wrong” row | Duplicate values in the first column. VLOOKUP uses the first one it finds. | Use a truly unique identifier. |
A reliable habit: if a lookup fails and the value looks right, suspect invisible formatting before you suspect the formula.
A practical use: cleaning up messy labels
VLOOKUP is not only for pulling prices or emails. A common marketing use is a mapping sheet. Say your data has several spellings of the same city, such as three versions of New York and two of Boston. Create a second sheet that lists every variation in one column and the single clean label you want (for example “Boston, MA”) in the next. Then add a “clean” column next to your data and use VLOOKUP to look up each messy entry on the mapping sheet. Your reports now show two tidy labels instead of five, which makes filtering and comparing performance much easier.
This works because the mapping sheet puts the messy value in the first column and the clean value to its right, exactly where VLOOKUP expects it. If you are building reports like this to track marketing results, our roadmap for learning digital marketing online shows where spreadsheet skills fit alongside the rest of the toolkit.
Pulling data from another sheet or workbook, and what to use instead
To look up data on another sheet, you use the same four arguments. The only change is that the table argument points to the other sheet. The easiest way is to start the formula, click over to the other sheet and select the range there, so Excel writes the reference for you. Working across two separate workbooks follows the same pattern. Reference formatting and link behavior can vary, so check Microsoft’s current VLOOKUP page if you hit trouble.
If the “only looks right” rule gets in your way, there are alternatives. Tech on the Net notes that XLOOKUP is the next-generation lookup function and works for both vertical and horizontal lookups, and HLOOKUP handles horizontal lookups. For details on features such as returning the last match or all matches, check Microsoft’s official documentation for the version of Excel you use, as availability depends on your version.
If you are curious about letting AI help with spreadsheet tasks like writing formulas or tidying data, Mastering Generative AI is a good next step.
Frequently asked questions
What is the VLOOKUP formula and what are its four arguments?
The formula is =VLOOKUP(lookup value, table, column number, TRUE or FALSE). The lookup value is what you search for, the table is the range to search, the column number says which column to return from, and the last argument chooses exact (FALSE) or approximate (TRUE) matching.
How do you use VLOOKUP to pull data from another sheet?
Build the formula as usual, but when you reach the table argument, click the other sheet and select the range there. Excel fills in the sheet reference for you. Just make sure the value you are searching for is in the first column of the range you select.
What is the difference between exact match (FALSE) and approximate match (TRUE) in VLOOKUP?
FALSE returns a result only when it finds an exact match, and shows #N/A otherwise. TRUE returns the next smaller value when there is no exact match, and requires the first column to be sorted in ascending order. TRUE is the default if you omit the argument, so type FALSE when you need exactness.
Why does VLOOKUP only look to the right of the lookup column?
VLOOKUP searches the first column of your table and returns a value from a column counted to the right of it. It cannot return data from a column to the left. The simple workaround is to rearrange your data so the lookup value is leftmost, or use a different function such as XLOOKUP, if your version of Excel includes it.
What can I use instead of VLOOKUP, such as XLOOKUP?
XLOOKUP is described as the next-generation lookup function and handles both vertical and horizontal lookups. HLOOKUP covers horizontal lookups. Check Microsoft’s current documentation for what your version of Excel supports.
Sources
- VLOOKUP function (Microsoft Support)
- How to Use VLOOKUP Function in Microsoft Excel (HubSpot)
- MS Excel: How to use the VLOOKUP Function (Tech on the Net)
- WorksheetFunction.VLookup method (Microsoft Learn)
Want to practice VLOOKUP on real data, along with filters, pivot tables and charts? Try the Learning to Master Excel: Formulas, VLOOKUPS, Charts, and more course. If you work in the cloud version of Excel, the Unlocking Microsoft Office 365 course covers that setup. You can start with the 7-day free trial and learn one lesson at a time.