Spreadsheet functions

VLOOKUP in WPS Spreadsheets: syntax, the 4th argument, and every error explained

Tested in WPS Office 12.1.2 Free on Ubuntu · 11 November 2026 · Lab-verified

In short

VLOOKUP is fully supported in WPS Spreadsheets 12.1 Free and returns the same values as Excel on the same workbook. Almost every VLOOKUP failure is caused by one of three things: omitting the FALSE argument, looking up text that is stored as a number, or trying to return a column to the left.

1. The short answer

VLOOKUP behaves in WPS exactly as it does in Excel — including its two classic limitations. There is no WPS-specific bug to work around, and no reason to convert VLOOKUP formulas if you move a workbook across.

2. What we tested

Three exact-match lookups on a six-row product table, run in a clean WPS Office 12.1.2 Free installation, compared against the values Microsoft Excel returns for the same formulas.

VLOOKUP test results: WPS Spreadsheets 12.1.2 versus Microsoft Excel
Test	Formula	WPS	Excel	Match
Exact match, return 2nd column	`=VLOOKUP("B-2001", A2:E7, 2, FALSE)`	Monitor 27in	Monitor 27in	identical
Exact match, return 4th column	`=VLOOKUP("C-3002", A2:E7, 4, FALSE)`	119	119	identical
Missing key wrapped in IFERROR	`=IFERROR(VLOOKUP("Z-9999", A2:E7, 2, FALSE), "Not found")`	Not found	Not found	identical

3. Syntax and why the 4th argument matters

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The fourth argument is where almost every real-world bug lives:

FALSE (or 0) — exact match. The formula returns the value only if it finds the key character-for-character. This is what you want for SKUs, invoice numbers, email addresses and names.
TRUE (or 1, or omitted) — approximate match. The formula assumes the first column is sorted ascending and returns the largest value that does not exceed the lookup value. Used deliberately for tax brackets and grade bands. Used accidentally, it silently returns the wrong row — no error, just a wrong answer.

The single highest-value habit: always type the fourth argument. If it is omitted, Excel and WPS both default to approximate match, and a lookup on an unsorted invoice list will return a plausible-looking number from the wrong row.

4. What each error actually means

`#N/A`

The key was not found. The three usual causes: the value exists but with a trailing space; the value is stored as text in one sheet and as a number in the other; or the reference points at the wrong sheet after a copy.

Diagnose with =LEN(A2) on both sides — if the lengths differ by one, you have a space. Fix with =TRIM().

`#REF!`

The column index is larger than the table. A five-column table asked for column 6, usually because someone inserted a column and the hard-coded 4 no longer points where it did.

Fix by making the index dynamic: =MATCH("Price", A1:E1, 0) returns the right number even after columns move.

`#VALUE!`

The column index is not a number. Usually a quoted "4" instead of 4, or a cell reference pointing at text.

Wrong row, no error

Approximate match on unsorted data. The most dangerous failure because it produces a number. The cell looks right; the invoice is wrong. This is the one worth auditing for before sending a file.

5. The left-lookup limitation

VLOOKUP can only return a column to the right of the lookup column. If your key is in column C and the value you want sits in column A, VLOOKUP cannot reach it. This is a design limit of the function, not a WPS limit — Excel behaves identically.

Two ways out:

Reorder the columns so the key is leftmost. Simple, but breaks the layout anyone else relies on.
Use INDEX/MATCH or XLOOKUP. Both look in either direction: =INDEX(A2:A7, MATCH("Desk Lamp", B2:B7, 0)) returns a column that sits to the left of the key.

Our XLOOKUP test shows that WPS 12.1 handles the modern function natively, including reverse lookups — which removes this limitation entirely.

6. Sources and method limits

Direct lab run: WPS Office 12.1.2.22571 Free on Ubuntu 24.04, captured 11 Nov 2026.
Workbook generated with openpyxl; expected answers written alongside the formulas.
Baseline: Microsoft Excel for Microsoft 365.
Not tested: lookups across external workbook links, or tables larger than six rows.

VLOOKUP specification at a glance

Function name: VLOOKUPHorizontal lookup equivalent: HLOOKUP
Default match: Approximate (TRUE)Must pass FALSE for exact match — the most common bug
Direction: Right onlyCannot return a column left of the lookup column
Modern alternative: XLOOKUPFully supported in WPS 12.1, removes both limits

Check current WPS plans and pricing

Opens the official WPS site. Spreadsheet formulas are in the free build.