In Excel, VLOOKUP searches for a value in the leftmost column of a range and returns a value from another column in the same row. With employee IDs in A2:A6, departments in C2:C6, and the ID to find in E2, enter =VLOOKUP(E2,A2:C6,3,FALSE); if E2 contains 104 and that employee’s department is Support, the result is Support.
What VLOOKUP Does
VLOOKUP is short for “vertical lookup”: it checks a column from top to bottom for a value, then retrieves related information from the matching row. It is useful when data is arranged in columns—for example, a product list with product codes, descriptions, prices, and stock quantities. Instead of scanning the list manually, a formula can return the requested detail for a code entered elsewhere in the workbook.
The function is available in Microsoft Excel for Microsoft 365 on Windows, as well as in other current Excel editions. It is also available in Excel for Mac and Excel for the web. The function’s basic behavior is the same across these versions, though ribbon layouts and some surrounding features can differ.
VLOOKUP does not search every column in a table. It searches only the first column in the range you supply. That design is the source of both its usefulness and its main limitation: the value you look up must be in a column to the left of the value you want returned.
How the VLOOKUP Arguments Work
A VLOOKUP formula has four arguments, in this order: the value to find, the range to search, the number of the return column within that range, and whether to use an exact or approximate match. In the opening example, E2 supplies the employee ID, A2:C6 is the data range, 3 means the third column of that range, and FALSE requests an exact match.
- Lookup value: The item Excel should search for, such as an ID in a cell or a text value.
- Table array: The range containing both the lookup column and the results to return. Its leftmost column must contain the lookup values.
- Column index number: The position of the result column counted from the left edge of the selected range. For A2:C6, column A is 1, B is 2, and C is 3.
- Match mode: FALSE requests an exact match. TRUE requests an approximate match. If this argument is left out, Excel uses approximate matching, so it is safer to specify the mode explicitly.
The column index is relative to the selected range, not the worksheet’s column letters. If the range begins in D, its first column is still index 1. Including this detail in a formula review can prevent a plausible-looking formula from returning the wrong field.
Build an Exact-Match Lookup Step by Step
For typical IDs, names, product codes, and other distinct labels, use an exact match. Set up the source list with the lookup key in the first column and the information you need in a column to its right. Then place the value to search for in a separate cell.
- Identify the lookup column and the column containing the answer you need.
- Select a range that includes both columns, starting with the lookup column on the left.
- Count the return column from the left edge of that range.
- Enter the lookup value’s cell reference, the range, the column number, and FALSE in the formula bar or a worksheet cell.
- Check the returned record against the source data, especially if the lookup value might appear more than once.
For instance, with product codes in A2:A100, product names in B2:B100, prices in C2:C100, and the code to search for in E2, use =VLOOKUP(E2,A2:C100,3,FALSE) to return the price. The formula looks for E2 in column A and returns the value from column C on the matching row. The 3 is counted from the beginning of A:C; it is not the worksheet’s column number C.
When copying a formula down a list, the lookup cell should usually change by row, but the source range should stay in place. You can keep the range fixed with dollar signs. With product codes and prices still in A2:C100 and lookup codes beginning in E2, use =VLOOKUP(E2,$A$2:$C$100,3,FALSE). The dollar signs prevent the source range from shifting as the formula is copied.
Exact Match and Approximate Match
FALSE is the right choice for most everyday lookups. It asks Excel to find the same value as the lookup value, rather than a nearby value. If no exact match exists, the usual result is #N/A. Exact matching is not case-sensitive, so Excel treats “abc” and “ABC” as equivalent for this function.
TRUE is intended for ordered categories such as tax bands, grading thresholds, or shipping brackets. In approximate mode, the first column of the table must be sorted in ascending order. Excel returns the row for an exact value, if present; otherwise, it uses the largest lookup-column value that is less than the lookup value. An unsorted table can therefore return an incorrect-looking answer without an obvious warning.
For example, suppose E2:E4 contains minimum scores 0, 50, and 80, and F2:F4 contains the labels Beginner, Intermediate, and Advanced. With a score of 72 in B2 and those sorted thresholds in E2:F4, use =VLOOKUP(B2,$E$2:$F$4,2,TRUE). The formula returns Intermediate because 50 is the greatest threshold not above 72. If B2 is below the smallest threshold, VLOOKUP returns #N/A; add a lower starting threshold if such values need a category.
Exact-match VLOOKUP also recognizes wildcard characters in text searches: an asterisk can stand for any number of characters, and a question mark can stand for one character. Use a tilde before a wildcard when you need to search for the character itself. This is useful for intentional partial-text searches, but it can cause surprises if a lookup value accidentally contains a wildcard.
Common VLOOKUP Errors and Fixes
A failed lookup often reflects a mismatch between the source value and the search value rather than a broken function. For example, the number 104 and the text value “104” can look identical in cells but may not match as expected. Leading or trailing spaces, inconsistent punctuation, and imported data with mixed formats can also interfere.
- #N/A: Excel did not find an exact match, or an approximate lookup value was below the smallest threshold. Check spelling, spaces, data type, and whether the searched value is actually in the first column of the range.
- #REF!: The column index points beyond the number of columns in the selected range. Expand the range or correct the index.
- #VALUE!: The column index is invalid, such as a value below 1, or an argument has an unsuitable value. Check the argument order and column number.
- A wrong but plausible result: Confirm that the final argument is FALSE for an exact lookup. If it is TRUE or omitted, Excel may use an approximate result.
Duplicates deserve a separate check. If the lookup column contains the same key in multiple rows, VLOOKUP returns the first matching row it encounters from the top. It does not warn that other matches exist, and it does not combine them. If each key is supposed to be unique, remove or investigate duplicates in the source data rather than relying on the first result.
For a user-friendly message when no exact match is found, you can wrap the lookup in IFERROR. With the same employee layout—IDs in A2:A6, departments in C2:C6, and the search ID in E2—use =IFERROR(VLOOKUP(E2,A2:C6,3,FALSE),"Not found"). If the ID does not match any row, the formula displays Not found. Use this deliberately: IFERROR can also conceal other formula problems, so it should not replace checking the source range and column index.
VLOOKUP’s Limits and Alternatives
VLOOKUP can return results only from columns to the right of its lookup column. If an employee name is in column A and the ID to search by is in column C, the function cannot search C and return A using a range arranged A:C. You could rearrange the data or choose a function that supports looking in one range and returning from another.
XLOOKUP, available in Microsoft 365 and Excel 2021 or later, is often a more flexible choice. It can search in one column and return a result from a separate column, including a column to the left, and exact matching is its default. With IDs in A2:A6, departments in C2:C6, and the search ID in E2, use =XLOOKUP(E2,A2:A6,C2:C6,"Not found"). The result is the matching department, or Not found if there is no match. Older Excel versions that do not include XLOOKUP can use INDEX and MATCH together for many of the same left-lookup situations.
VLOOKUP remains a reasonable option when a workbook already uses it, the lookup key is on the left, and a simple return column is all that is needed. It is also widely recognized in existing spreadsheets. For a new workbook, consider whether column insertions could make a hard-coded column index confusing, or whether you need multiple matches rather than just the first one. Choosing a function based on those needs is more useful than replacing every VLOOKUP automatically.
Habits That Make Lookups More Reliable
A lookup formula can only be as dependable as the data and range behind it. Keep keys consistent, avoid extra spaces, and use a clearly defined source list. If the source list changes often, an Excel Table can make the data easier to manage because its range expands as rows are added. Before sharing a result, check a few returned records directly against their source rows.
- Use FALSE explicitly for ordinary exact lookups.
- Keep the lookup key as the first column of the VLOOKUP range.
- Use fixed references when copying formulas so the source range does not drift.
- Confirm whether keys are unique, and decide how duplicates should be handled.
- Do not replace every error with a blank or message until you know what caused it.
These checks matter when VLOOKUP is used to combine lists, such as adding department names to an employee export. A missing result can signal a formatting issue or a genuinely absent record. Treating every missing value as a harmless formula error can hide a data-quality problem that needs follow-up.
Frequently Asked Questions
Does VLOOKUP search to the left?
No. VLOOKUP searches the leftmost column in its selected range and returns a value from a column to its right. Use XLOOKUP or INDEX and MATCH if the result is to the left of the lookup column.
Why does VLOOKUP return #N/A when the value is visible?
The displayed values may differ in a way that is hard to see: one could be stored as text, or a cell could contain extra spaces or different punctuation. Also verify that the lookup value is in the first column of the selected range and that FALSE is specified for an exact match.
What does the number 3 mean in a VLOOKUP formula?
It means return the third column within the table range. For a range A:C, that is worksheet column C. If the range starts at D, the third column is F.
Can VLOOKUP return more than one matching row?
Not by itself. It returns the first matching row. If you need every matching record, use a filtering approach or another function designed to return multiple results, if it is available in your Excel version.
Should I use VLOOKUP or XLOOKUP?
Use VLOOKUP when its left-to-right layout suits the data or you need compatibility with an older workbook. XLOOKUP is generally more flexible for newer Excel versions because it separates the lookup range from the return range and can search left or right.
Related
- Convert PowerPoint to Excel: Steps, Options, and Limits
- Access Database Import from Excel: Steps and Tips
- Excel Attendance Format: A Clear Monthly Attendance Tracker
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel MAX IF: Find the Highest Number by ConditionTo find the largest number in Excel that meets a condition, use MAXIFS, such as =MAXIFS(C2:C7,B2:B7,"East"). If you…
- Excel Table Filter Button Greyed Out? Fixes That WorkIf Excel’s filter button is greyed out, first press Esc to leave cell-edit mode, ungroup any selected worksheets,…
- DAX vs Excel Formulas: How They Differ and When to UseDAX and Excel formulas both calculate results, but they work in different places and answer different kinds of…
- Excel CONCAT Formula: Combine Two Columns With a SpaceTo combine two columns in Excel, enter =CONCAT(A2," ",B2) in a blank cell, replacing A2 and B2 with…
- Excel Sort Shortcut: Sort Your Data Quickly and SafelyIn Excel for Microsoft 365 on Windows, press Alt, A, S, S in sequence to open the Sort…
- Excel Cell Protection Options: Lock Cells the Right WayExcel cell protection has three main parts: mark cells as Locked or Hidden, then use Protect Sheet to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.