Free tools Windows power users keep installed
One-click scans. No signup required.
Some links on this page are affiliate links: if you buy through them we may earn a commission, at no extra cost to you.
VLOOKUP searches for a value in the first column of a range and returns related information from another column in the same row. For most lookups involving IDs, product codes, names, prices, or departments, use an exact-match formula such as:
=VLOOKUP(A2,$F$2:$H$100,3,FALSE)The final FALSE is important: it tells Excel to require an exact match instead of making an approximate guess.
What VLOOKUP does
VLOOKUP is useful when one worksheet area contains a value you know—such as a product ID—and another table contains that value alongside information you want to retrieve.
| A: Product ID | B: Product | C: Price returned |
|---|---|---|
| P-100 | Keyboard | 49.99 |
| P-101 | Mouse | 19.99 |
| D: Product ID | E: Product | F: Price |
|---|---|---|
| P-100 | Keyboard | 49.99 |
| P-101 | Mouse | 19.99 |
To return the price for the product ID in A2, enter this formula in C2:
#1 Best Overall
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
=VLOOKUP(A2,$D$2:$F$3,3,FALSE)Excel looks for A2 in column D, finds the matching row, and returns the value from column F. The result for P-100 is 49.99.
VLOOKUP searches only from left to right. The lookup column must be the first column of the selected range, and the function cannot directly return a value from a column to its left. See Microsoft’s VLOOKUP documentation for the official syntax and behavior.
VLOOKUP syntax explained
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])| Argument | What it means | Example |
|---|---|---|
lookup_value |
The value Excel should find. | A2 |
table_array |
The range containing both the lookup column and the return column. | $F$2:$H$100 |
col_index_num |
The return column’s position within the selected range, counted from left to right. | 3 |
range_lookup |
Whether Excel should find an exact or approximate match. | FALSE |
lookup_value
This can be a cell reference, number, or text string:
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
=VLOOKUP(A2,$F$2:$H$100,2,FALSE)=VLOOKUP(102,$F$2:$H$100,2,FALSE)=VLOOKUP("P-100",$F$2:$H$100,3,FALSE)The lookup value must exist—or be eligible to match—in the first column of table_array.
table_array
This is the complete range containing the search column and the answer column. In $F$2:$H$100, Excel searches column F and can return values from F, G, or H.
The dollar signs make the range absolute. That prevents it from moving when you copy the formula down. Without them, a formula copied to the next row could start searching F3:H101 instead of the intended fixed reference table.
col_index_num
This number counts columns inside the selected range, not columns across the worksheet. For $F$2:$H$100:
- F is column 1.
- G is column 2.
- H is column 3.
Therefore, 3 means “return the third column of the selected range,” which happens to be worksheet column H. It does not mean worksheet column C or a globally numbered column F.
range_lookup
Use:
FALSEor0for an exact match.TRUEor1for an approximate match.
If you omit this argument, Excel uses approximate matching. That is a frequent cause of incorrect results, so do not leave it blank unless approximate matching is intentional.
How to create a basic VLOOKUP formula
- Put the value to search for in a cell, such as
A2. - Arrange the reference table so its lookup column is on the left.
- Select the cell where the result should appear.
- Type
=VLOOKUP(. - Select the lookup value, such as
A2, and type a comma. - Select the full lookup table, including the search and return columns.
- Press F4 to make the range absolute, or add dollar signs manually.
- Enter the return column’s position within that range.
- Enter
FALSEfor an exact match. - Close the parenthesis and press Enter.
For example:
=VLOOKUP(A2,$F$2:$H$100,3,FALSE)This searches for the value in A2 in column F and returns the corresponding value from column H.
Exact-match VLOOKUP: the safest default
Exact matching is appropriate for product IDs, employee IDs, invoice numbers, customer numbers, ZIP codes, email addresses, SKU codes, and other discrete identifiers. The lookup column does not need to be sorted.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →=VLOOKUP(A2,$F$2:$H$100,3,FALSE)0 is equivalent to FALSE:
=VLOOKUP(A2,$F$2:$H$100,3,0)Using FALSE makes the formula’s intent easier to read. It also protects you from the approximate-match default that applies when the fourth argument is omitted.
Copying a VLOOKUP formula safely
Suppose:
A2:A10contains product IDs.F2:F100contains product IDs in the reference table.H2:H100contains prices.
Enter this in B2:
=VLOOKUP(A2,$F$2:$H$100,3,FALSE)When copied to B3, it should become:
=VLOOKUP(A3,$F$2:$H$100,3,FALSE)The lookup reference changes from A2 to A3, while the reference table remains fixed. If the table range changes as you fill down, add absolute references with F4.
Approximate-match VLOOKUP
Approximate matching is designed for thresholds and bands rather than unique IDs. Typical uses include tax brackets, commission rates, shipping bands, grades, discounts, and score classifications.
| Minimum score | Rating |
|---|---|
| 0 | Fail |
| 60 | Pass |
| 80 | Good |
| 90 | Excellent |
With the table in F2:G5, use:
=VLOOKUP(A2,$F$2:$G$5,2,TRUE)If A2 is 85, Excel returns Good. It finds the largest value in the first column that is less than or equal to 85.
The first column must be sorted in ascending order for approximate matching. An unsorted threshold table can produce an unexpected result. Also remember that this formula is approximate:
=VLOOKUP(A2,$F$2:$G$5)That is because omitting range_lookup makes Excel use TRUE. Treat the omission as a deliberate design choice, not a shortcut. Microsoft explains the exact and approximate match rules in its guidance on correcting VLOOKUP #N/A errors.
Using VLOOKUP with an Excel Table
Formatting the reference data as an Excel Table can make a workbook easier to maintain. If the table is named Products, a structured-reference formula could be:
=VLOOKUP([@ProductID],Products,3,FALSE)The column number still counts from the table’s leftmost column. If the table’s column order changes, a manually counted index can become difficult to audit.
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Clear out junk files and repair common Windows errors3Fix the driver behind crashes, sound loss and screen glitchesFor a readable formula that names the lookup and return columns separately, XLOOKUP is usually clearer:
=XLOOKUP([@ProductID],Products[ProductID],Products[Price],"Not found")Why VLOOKUP returns an error or the wrong result
| Result | Likely cause | What to check |
|---|---|---|
#N/A |
No match, inconsistent data, or an unsuitable approximate lookup. | Check the key, data type, spaces, leading zeros, and match mode. |
#REF! |
The column index exceeds the selected range. | Count the columns inside table_array. |
#VALUE! |
Invalid range or a column index of zero or less. | Check the range and index argument. |
#NAME? |
Text was entered without quotation marks, or a function name is misspelled. | Put literal text in quotation marks and check spelling. |
| Wrong value | Approximate matching was used unintentionally. | Add FALSE and verify the formula’s range. |
Fixing #N/A
Work through this checklist:
- Confirm that the key exists in the first column of the selected range.
- Confirm that both values use the same data type. A number such as
123may not match text such as"123". - Remove unwanted spaces with
TRIMwhere appropriate:
=TRIM(A2)Nonprinting characters can sometimes be removed with:
=CLEAN(A2)Use these as data-cleaning steps rather than assuming the lookup formula itself is defective. For a numeric conversion, VALUE(A2) may help; to convert a value to text, A2&"" can help. Apply the same treatment consistently to both sides of the lookup.
Codes with leading zeros, such as 00125, should normally remain text in both tables. If one side is converted to a number, the zeros may disappear and the values may no longer match.
Dates can also fail to match when the displayed dates look identical but their stored values differ—for example, when one value includes a time component. Exact matching compares the underlying values, not only their formatting.
Rank #4
Using IFERROR carefully
To display a friendlier message:
=IFERROR(VLOOKUP(A2,$F$2:$H$100,3,FALSE),"Not found")IFERROR catches errors including #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!. However, it does not repair the underlying data or formula. It can hide an incorrectly selected range or an unexpected error, so use it after validating the lookup.
Fixing #REF!
If the range is F:H, it contains only three columns. This formula is invalid:
=VLOOKUP(A2,$F$2:$H$100,4,FALSE)Change the index to a number from 1 to 3, or expand the selected range to include a fourth column.
Recommended Free Tools
Fixing #VALUE!
Check that the table range is valid and contains at least one column. The index must be at least 1. Also inspect any nested formula that supplies the lookup value or column index.
Important edge cases
Duplicate lookup values
If the lookup column contains duplicate keys, VLOOKUP is not a way to return every matching row. Make the identifier unique if one result is intended. If you need all matching records and your Excel version supports dynamic arrays, consider:
=FILTER($G$2:$H$100,$F$2:$F$100=A2,"Not found")A successful lookup can also return a blank-looking result when the matched return cell is empty. A visually blank result does not necessarily mean that the lookup failed.
Wildcards in text lookups
VLOOKUP supports wildcards for text searches. An asterisk matches any sequence of characters, while a question mark matches one character:
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware match=VLOOKUP("AB*",$F$2:$H$100,3,FALSE)To search for a literal asterisk, escape it with a tilde:
=VLOOKUP("AB~*",$F$2:$H$100,3,FALSE)Entire-column references
An explicit range is usually easier to audit than a full-column reference. In some modern Excel scenarios, using an entire column with implicit intersection can also contribute to a #SPILL! issue. Prefer:
=VLOOKUP(A2,A:C,2,FALSE)over using an entire-column lookup value such as A:A when a single-cell reference is intended.
VLOOKUP’s main limitations
- Left-to-right only: the lookup column must be the leftmost column of the selected range.
- Manual column counting: the return index can become fragile when columns are inserted or reordered.
- One result per lookup: duplicate keys do not produce a list of all matches.
- Approximate matching requires sorted data: an unsorted threshold table can return a wrong result.
- Data quality matters: spaces, hidden characters, number-versus-text differences, and lost leading zeros can prevent matches.
VLOOKUP versus XLOOKUP, INDEX/MATCH, FILTER, and Power Query
| Requirement | Best fit | Why |
|---|---|---|
| Legacy compatibility and a conventional left-to-right lookup | VLOOKUP | Widely recognized and supported in older workbooks. |
| Exact matching with flexible direction | XLOOKUP | Defaults to exact matching, can look left or right, and supports a not-found result. |
| Separate row and column logic in older Excel | INDEX/MATCH | Flexible lookup construction without relying on XLOOKUP. |
| Multiple matching rows | FILTER | Returns all qualifying records in supported dynamic-array versions. |
| Recurring imports, cleaning, or joins | Power Query | Creates a refreshable data workflow instead of relying on copied formulas. |
XLOOKUP
For a new workbook where compatibility permits, XLOOKUP avoids the leftmost-column restriction and manual index counting:
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →=XLOOKUP(A2,$F$2:$F$100,$H$2:$H$100,"Not found")Its syntax is:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Microsoft describes XLOOKUP as an improved alternative to VLOOKUP, but check the target workbook’s Excel version and deployment environment before replacing a legacy formula. Do not assume every Excel installation supports it.
INDEX/MATCH
This combination is useful when the workbook already uses it or when older-version compatibility and flexible lookup direction matter:
=INDEX($H$2:$H$100,MATCH(A2,$F$2:$F$100,0))The 0 in MATCH requests an exact match. Microsoft’s comparison of VLOOKUP, INDEX, MATCH, and XLOOKUP provides further guidance on choosing among these approaches.
Which Excel option do you need?
You do not need a paid Microsoft 365 plan merely to use VLOOKUP. Microsoft provides Excel for the web at no charge with a Microsoft account, although browser and desktop capabilities are not identical.
- Occasional practice or basic browser work: Free Excel for the web may be sufficient.
- Desktop Excel, offline access, and current features: Microsoft 365 Personal may be suitable.
- Several household users: Microsoft 365 Family may make sense if each person needs access.
- No recurring subscription: Office 2024 is a one-time-purchase option, but major-version upgrades are not included in the same way as a Microsoft 365 subscription.
Availability, plan terms, and prices vary by country and can change. Compare the current Microsoft offers before buying. Microsoft’s explanation of the difference between free web apps and Microsoft 365 subscriptions is a useful starting point.
Compatibility
Microsoft currently lists VLOOKUP for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016, including Mac editions. Existing workbooks may therefore continue to use VLOOKUP even when newer formulas are available. Check the workbook’s target users before introducing XLOOKUP or dynamic-array functions.
For a straightforward exact lookup, the practical formula remains:
=VLOOKUP(A2,$F$2:$H$100,3,FALSE)Verify four things before copying it: the key is in the range’s first column, the range is anchored, the return index is counted within the range, and the fourth argument explicitly states the intended match type.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
