VLOOKUP Examples in Excel: Formulas That Work

VLOOKUP finds a value in the first column of a range and returns a value from another column in the same row. For an exact match, with employee IDs in A2:A5, names in B, departments in C, salaries in D, and the ID to find in F2, enter =VLOOKUP(F2,A2:D5,4,FALSE); if F2 contains E104 and that employee’s salary is 68000, the result is 68000.

How VLOOKUP works

VLOOKUP’s name means “vertical lookup”: it searches down the leftmost column of a selected range. Its arguments, in order, specify the value to find, the complete range to search, the number of the return column within that range, and whether to use exact or approximate matching. In the opening example, A2:D5 is the range and D is its fourth column, so the column number is 4.

The return-column number is relative to the selected range, not the worksheet. If the range begins in column C, its first column is number 1, even though it is column C on the sheet. The lookup value must be in the range’s first column; VLOOKUP cannot search a column to the right and return a value from a column to its left.

Lookup value: the ID, code, name, or other value being searched for.
Table array: the range containing both the search column and the return column.
Column index: the position of the result column within that range.
Match option: FALSE requests an exact match; TRUE requests an approximate match.

Exact-match examples for IDs and products

For codes, names, and most ordinary lookups, use FALSE as the final argument. Leaving that argument out can make Excel use approximate matching, which may return an unexpected result if the first column is not sorted appropriately. The product example below uses a small inventory table with product codes in A2:A5, product names in B, categories in C, and prices in D.

With those product codes in A2:A5, prices in D2:D5, and the requested code in F2, enter =VLOOKUP(F2,A2:D5,4,FALSE). If F2 contains P-204 and that row’s price is 19.99, the formula returns 19.99. A practical advantage of using the whole A:D range is that the formula can return other fields too: change the column index to 2 for the name or 3 for the category.

For example, with the same A2:D5 inventory table and P-204 in F2, =VLOOKUP(F2,A2:D5,2,FALSE) returns the product name in column B for that code. The search column must still be the first column of the chosen range. If the product code is actually in column C and the desired price is in D, select C2:D5 and use column index 2, rather than selecting A:D and expecting VLOOKUP to search column C.

Looking up records with two conditions

VLOOKUP accepts one lookup value, but you can combine two fields into a key. This is useful when a product appears in multiple regions, or the same person has several records. A helper column is often the clearest approach: it creates one combined key for each row, and the lookup combines the two requested values in the same way.

Suppose B2:B5 contains product names, C2:C5 contains regions, D contains dates, and E contains sales. With product and region in B2 and C2, enter this in A2 and fill it down through A5: =B2&"|"&C2. For a row where B2 is Widget and C2 is West, the helper key is Widget|West. The vertical bar separates the fields and helps avoid accidental collisions such as combining AB with C and A with BC.

With the helper keys in A2:A5, sales in E2:E5, the requested product in F2, and the requested region in G2, use =VLOOKUP(F2&"|"&G2,A2:E5,5,FALSE). If Widget|West is associated with sales of 120, the result is 120. Ensure the helper key is unique if each combination should identify exactly one row; otherwise VLOOKUP returns the first matching row.

Approximate match for grades and thresholds

Approximate matching is useful when a value belongs to a range, such as a score-to-grade table or a commission band. In this mode, VLOOKUP finds the largest first-column value that is less than or equal to the lookup value. The first column of the table must be sorted in ascending order. If it is not, the answer can be wrong even when the formula has no visible error.

Suppose A2:B6 contains these minimum scores and grades: 0/F, 60/D, 70/C, 80/B, and 90/A. With the score 84 in E2 and that table in A2:B6, enter =VLOOKUP(E2,A2:B6,2,TRUE). The result is B, because 80 is the greatest threshold that does not exceed 84. A score of 90 returns A; a score below the first threshold has no valid lower band and may return an error.

TRUE is the approximate-match setting, and it is also the default if the final argument is omitted. For clarity, write TRUE explicitly when the table is a threshold table. Do not use approximate matching for employee IDs, product codes, or other values that are labels rather than ordered thresholds.

Wildcard searches for partial text

With an exact-match setting, VLOOKUP can also use wildcards when searching text. An asterisk represents any number of characters, while a question mark represents one character. This can help find a name or description when only part of it is known, although a wildcard lookup returns the first matching row rather than every match.

Suppose A2:A5 contains customer names and B2:B5 contains account regions. With the names in A2:A5, regions in B2:B5, and the known prefix Northwind in F2, enter =VLOOKUP(F2&"*",A2:B5,2,FALSE). If the first matching name is Northwind Traders and its region is West, the result is West. If multiple names begin with Northwind, sort or filter the source data as needed to identify which first match is returned. The tilde character can be used to treat a wildcard character as a literal character in a search string.

Fixing common VLOOKUP errors

#N/A usually means an exact match was not found. Check for leading or trailing spaces, misspellings, and mismatched data types: a number stored as text is not always equivalent to the same numeric value. Also confirm that the lookup value is present in the first column of the selected range. If a missing result is normal, you can display a useful message instead of the error.

With employee IDs in A2:A5, salaries in D2:D5, and the requested ID in F2, enter =IFNA(VLOOKUP(F2,A2:D5,4,FALSE),"Not found"). If F2 contains an ID absent from A2:A5, the result is Not found; if it matches E104 in the example table, the result remains 68000. IFNA handles #N/A specifically. IFERROR can hide other formula errors too, so it may conceal a broken range or invalid column number that you would rather diagnose.

#REF! commonly appears when the column index is larger than the number of columns in the selected range, or when referenced cells have been deleted. #VALUE! can indicate an invalid argument, including a column index below 1. A wrong but plausible result often points to TRUE or an omitted match argument, an unsorted approximate-match table, or a column index counted from the worksheet instead of from the selected range.

When copying a formula down, consider whether the table range should stay fixed. For instance, dragging a formula may shift A2:D5 to A3:D6. You can make the range absolute by using dollar signs, as in =VLOOKUP(F2,$A$2:$D$5,4,FALSE). With the employee table in A2:D5 and the lookup ID in F2, this still returns 68000 for E104, and the table address remains fixed when the formula is copied. The lookup cell F2 remains relative, so it changes as the formula is filled down.

VLOOKUP limitations and alternatives

VLOOKUP is widely supported and easy to read, but it has structural limits. It searches only the first column of its table range, returns values only from columns to the right, and uses a numbered return column that can become incorrect if someone inserts or rearranges columns within the range. For a small, stable table, these constraints may be acceptable. For a changing workbook, make the range and its column layout easy to audit.

Microsoft 365 includes XLOOKUP, which can search one range and return from another without a column index, and it can return a value from either side of the search column. With employee IDs in A2:A5, salaries in D2:D5, and the requested ID in F2, =XLOOKUP(F2,A2:A5,D2:D5,"Not found") returns 68000 for E104 or Not found when there is no match. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; it is not available in older perpetual versions such as Excel 2019. INDEX with MATCH is another option in older Excel versions when a lookup needs to return a value from the left.

Good habits for reliable lookup tables

Use FALSE for exact-match IDs, product codes, and names; use TRUE only for deliberately sorted threshold tables.
Keep the lookup field as the first column of the VLOOKUP range and count the return column from the left edge of that range.
Use a header row to make the fields clear, but select the data rows for the formula range when the header is not a valid lookup value.
Check that codes use consistent types and formatting. A number stored as text can behave differently from a numeric value.
Test a known match, a missing value, and a value near a threshold before relying on a lookup across a large dataset.

In Excel for Microsoft 365 on Windows, type the formula in the result cell and press Enter; use the fill handle to copy it to nearby rows. The same VLOOKUP formulas work in Excel for Mac and Excel for the web, though ribbon and keyboard details can differ. If a table will grow, consider converting the data range to an Excel table so its structure is easier to manage, and review formulas after inserting or rearranging columns.

Frequently Asked Questions

What does the last FALSE in VLOOKUP mean?
FALSE tells Excel to require an exact match for the lookup value. It is the usual choice for IDs, product codes, and names. If there is no exact match, VLOOKUP normally returns #N/A.

Can VLOOKUP return a value to the left?
No. VLOOKUP searches the first column of its range and returns a value from a column to its right. Use XLOOKUP in supported Excel versions, or INDEX with MATCH in older versions, when the result is to the left of the search column.

Why does VLOOKUP return the wrong row?
A frequent cause is using TRUE or leaving out the final argument when you intended an exact match. With approximate matching, the first column must also be sorted in ascending order. Confirm the match setting and the table’s sort order.

How do I make a VLOOKUP range stay fixed when copying?
Add dollar signs to the range reference, such as $A$2:$D$5. This keeps the table range in place as you fill the formula down, while a relative lookup cell such as F2 can change for each row.

Does VLOOKUP work in Excel for Mac and the web?
Yes. VLOOKUP is available in Excel for Microsoft 365 on Windows, Excel for Mac, and Excel for the web. The same formula syntax applies, although some interface and keyboard details vary by platform.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.