Master these four parameters, and you master the core logic of Excel.
=VLOOKUP(
"P003"
,
$A$1:$D$6
,
4
,
FALSE
)
"P003" is the lookup value — VLOOKUP searches for this in the first column (Column A) of the table, scanning row by row until it finds a match.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product ID | Product | Category | Price |
| 2 | P001 | Laptop | Electronics | $1,299 |
| 3 | P002 | Desk Chair | Furniture | $459 |
| 4 | P003 | Monitor | Electronics | $549 |
| 5 | P004 | Keyboard | Accessories | $89 |
| 6 | P005 | Webcam | Accessories | $129 |
With WPS AI Spreadsheet, you don't need to memorize VLOOKUP syntax. Simply describe what you want in plain language, and AI writes the formula for you — instantly.
You say:
"Look up employee E003's salary from the data table"
AI generates:
=VLOOKUP("E003", $A$1:$D$6, 4, FALSE)
"Find the salary for employee E003 from my table"
=VLOOKUP("E003", $A$1:$D$6, 4, FALSE)
Even experienced users run into these. Learn what causes each error and how to solve it in seconds.
Most common VLOOKUP error
Why it happens:
How to fix:
TRIM() to remove extra spacesIFERROR(VLOOKUP(...), "Not found")Column number exceeds range
Why it happens:
col_index_num is larger than the number of columns in your rangeHow to fix:
table_array to include more columnsCOLUMNS(A1:D1) to verify your range widthWrong parameter type
Why it happens:
col_index_num is less than 1lookup_value exceeds 255 charactersHow to fix:
col_index_num ≥ 1LEN()No error shown, but data is incorrect
Why it happens:
$ signs (shifts when copied)How to fix:
FALSE for exact match (99% of cases)$A$1:$D$6Use =IFERROR(VLOOKUP(...), "Not found") to gracefully handle errors and keep your spreadsheet clean. WPS AI can add this automatically when generating formulas.
WPS Office Spreadsheet provides full compatibility with Microsoft Excel — VLOOKUP, HLOOKUP, INDEX/MATCH, XLOOKUP, and 400+ functions all work seamlessly.
Open & edit .xlsx files, use the same shortcuts, and access your spreadsheets on Windows, Mac, Linux, Android & iOS. The built-in WPS AI can even write & debug VLOOKUP formulas for you.