To use VLOOKUP, type =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) — for example, =VLOOKUP(“E103”, A2:D6, 3, FALSE) returns the value from the third column of the row whose first column equals E103.
VLOOKUP, short for “vertical lookup,” searches for a value in the leftmost column of a range and returns a value from another column in the same row. It is one of the most widely used functions in Excel, and it behaves the same way in Microsoft 365, Excel 2021, and Excel for the web.
The VLOOKUP syntax explained
The function takes four arguments, and the last one is optional:
- lookup_value — the value you want to find. It can be a number, text, or a cell reference.
- table_array — the range that holds your data. The lookup value must sit in the first column of this range.
- col_index_num — the column number inside the range (not the worksheet letter) whose value you want returned. The first column of the range is 1.
- [range_lookup] — FALSE (or 0) for an exact match, TRUE (or 1) for an approximate match. If you leave it out, Excel assumes TRUE, which is a frequent cause of wrong answers.
A worked example
Suppose you have an employee table in the range A1:D6 and you want to look up details by employee ID.
| Column A (ID) | Column B (Name) | Column C (Department) | Column D (Salary) |
|---|---|---|---|
| E101 | Ava Patel | Sales | 58000 |
| E102 | Liam Chen | Marketing | 61000 |
| E103 | Noah Diaz | Finance | 72000 |
| E104 | Mia Rossi | Support | 49000 |
| E105 | Zoe Khan | Finance | 67000 |
To find the department for employee E103, write =VLOOKUP(“E103”, A2:D6, 3, FALSE), which returns “Finance.” To return the salary instead, change the column index to 4: =VLOOKUP(“E103”, A2:D6, 4, FALSE) returns 72000. Because the ID lives in column A — the first column of the range — VLOOKUP can find it and read across the row to the column you name.
Step by step
- Click the cell where you want the result to appear.
- Type =VLOOKUP( to begin the function.
- Enter or click the cell that holds the value you are searching for, then type a comma.
- Select the table range. Press F4 to lock it with dollar signs, for example $A$2:$D$6, so it does not shift when you copy the formula down.
- Type the column number you want returned, then a comma.
- Type FALSE for an exact match, close the parenthesis, and press Enter.
Exact match versus approximate match
Use FALSE for almost everything — IDs, names, SKUs, and email addresses. Use TRUE only when the first column is sorted in ascending order and you want the nearest value that is less than or equal to the lookup value, such as assigning a grade band or a tax bracket. With TRUE on unsorted data, Excel can return a value that looks plausible but is wrong, so treat approximate match as the exception rather than the default.
Common errors and how to fix them
| Error | Usual cause | Fix |
|---|---|---|
| #N/A | Value not found in the first column | Check spelling and spaces; confirm the value is in column 1 of the range and that range_lookup is FALSE |
| #REF! | col_index_num is larger than the number of columns in the range | Lower the column number so it fits inside table_array |
| #VALUE! | col_index_num is less than 1 or an argument is malformed | Use a whole number of 1 or more and recheck each argument |
| Wrong result | A number is stored as text, or range_lookup was left as TRUE | Convert text to numbers and add FALSE as the final argument |
Looking up on more than one column
VLOOKUP matches a single column. To match on two conditions, either add a helper column that joins the two keys with an ampersand, such as =A2&”|”&B2, and look up the combined key, or switch to XLOOKUP, which can match a concatenated array directly. A clean layout makes this easier, and features like merging cells for report titles keep the sheet readable without disturbing the data below.
Using VLOOKUP across sheets and workbooks
The table_array does not have to be on the same sheet. To pull data from another worksheet, include the sheet name in the reference, for example =VLOOKUP(A2, Prices!$A$2:$C$500, 3, FALSE), where “Prices” is the tab that holds the lookup table. You can also reference another open workbook, though Excel then stores an external link that must be updated whenever the source file changes. For anything you share widely, keeping the lookup table on a tab in the same workbook is more robust than pointing at a separate file that colleagues may not have.
XLOOKUP: the modern alternative
If your version supports it, XLOOKUP is generally the better choice. Its basic syntax is =XLOOKUP(lookup_value, lookup_array, return_array). It can look to the left as well as the right, returns an exact match by default, and lets you supply a fallback for a missing value with a fourth argument. For example, =XLOOKUP(“E103”, A2:A6, C2:C6) returns the department without counting columns, and =XLOOKUP(“E999”, A2:A6, C2:C6, “Not found”) shows a friendly message instead of #N/A. VLOOKUP is still worth learning because it appears in countless existing workbooks and runs in Excel 2019 and earlier, where XLOOKUP is not available. Knowing both means you can read old spreadsheets and write cleaner new ones.
Tips for reliable lookups
Keep your lookup column free of leading spaces and mixed data types — a number stored as text will not match a genuine number. When you need to hand a result to a colleague, capturing the sheet as an image is often faster than exporting; if you are on a Mac, our guide on taking a screenshot on a Mac covers the shortcuts. And if you get stuck on syntax, an AI assistant built on a large language model can draft a formula for you, though you should always test the output against your own data before trusting it.
