Lookup formula page
VLOOKUP Exact Match Formula
Use VLOOKUP exact match with FALSE when the lookup value is in the first column and the return value is to the right. This page shows Excel and Google Sheets syntax, sample data, expected returns, and common #N/A fixes.
Create a VLOOKUP exact match formula with FALSE for IDs, SKUs, names, or emails.
If F2 is A-100, the formula returns 49 from the Price column. If no SKU matches, VLOOKUP returns #N/A unless you wrap it in IFERROR.
Copy formulas
=VLOOKUP(F2, A2:D100, 4, FALSE)=VLOOKUP(F2, A2:D100, 4, FALSE)Excel and Google Sheets both use FALSE as the fourth VLOOKUP argument for an exact match. The lookup key must be in the first column of the selected table in both apps.
Example data
| SKU | Item | Category | Price | Stock |
|---|---|---|---|---|
| A-100 | Keyboard | Hardware | 49 | 18 |
| A-101 | Mouse | Hardware | 25 | 32 |
| B-200 | Desk Mat | Office | 18 | 9 |
| B-201 | Notebook | Office | 7 | 64 |
If F2 is A-100, the formula returns 49 from the Price column. If no SKU matches, VLOOKUP returns #N/A unless you wrap it in IFERROR.
How the formula works
- F2 is the lookup value, such as a SKU, customer ID, email, or product code.
- A2:D100 is the table range, and its first column must contain the lookup key.
- 4 returns the fourth column inside A2:D100, which is the Price column in the sample data.
- FALSE forces exact match so Excel or Google Sheets does not return a nearby sorted-row result.
| Syntax piece | Role in the formula |
|---|---|
| F2 | The SKU or other key to find in the first column of the lookup table. |
| A2:D100 | The lookup table; column A contains the key and column D contains the price returned here. |
| 4 | Returns the fourth column inside A2:D100, not necessarily worksheet column D in every copied version. |
| FALSE | Requires an exact match and avoids a nearby result from an approximate lookup. |
Verified examples
=VLOOKUP(F2, A2:D100, 4, FALSE)Excel: Enter A-100 in F2 and use the sample product table in A2:D5. Returns: 49
=IFERROR(VLOOKUP(F2, A2:D100, 4, FALSE), "Not found")Google Sheets: Enter C-999 in F2; that SKU is not in the sample table. Returns: Not found
=VLOOKUP(F2, A2:D100, 2, FALSE)Google Sheets: Enter B-200 in F2 and return the second table column. Returns: Desk Mat
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| VLOOKUP returns #N/A for a visible SKU | The two keys use different data types or one contains leading, trailing, or nonprinting spaces. | Compare the key cells directly, normalize text with TRIM or CLEAN, and confirm F2 exists in column A. |
| VLOOKUP returns #REF! | The column index is wider than the selected table; for example, 5 cannot be returned from A2:D100. | Use an index from 1 through 4 for A2:D100 or widen the table range to include the required return column. |
| VLOOKUP returns the wrong product | The final FALSE argument was omitted or changed to TRUE, enabling approximate match. | Restore FALSE for IDs, SKUs, names, and other keys that must match exactly. |
When not to use this formula
- Use XLOOKUP or INDEX MATCH when the return column is to the left of the key, and use FILTER when every matching row must be returned.
Alternatives
| Alternative | When to use it |
|---|---|
| XLOOKUP Exact Match Formula | Use when the workbook supports XLOOKUP and you want separate lookup and return ranges plus a built-in fallback. |
| INDEX MATCH Formula | Use when column insertions or a left-side return make a numeric VLOOKUP column index fragile. |
Related formulas
Official references
- VLOOKUP function from Microsoft
FAQ
What does FALSE change in this VLOOKUP?
FALSE requires the value in F2 to match a key in the first table column exactly. It is the safe choice for SKUs, IDs, emails, and names.
Why is the price column numbered 4?
The column number is counted inside A2:D100: A is 1, B is 2, C is 3, and D is 4.
Can this formula return every matching row?
No. VLOOKUP returns one value from the first exact match. Use FILTER or QUERY when duplicate keys should return multiple rows.