VLOOKUP
Quirk foundCategory: Lookup and reference · Last tested 2026-07-04
Support matrix
| Engine | Documented | Live-tested | Verdict |
|---|---|---|---|
| Excel | Yes | Not yet | n/a |
| Google Sheets | Yes | Not yet | n/a |
| LibreOffice Calc | Yes | Yes (24.2.7.2, 2026-07-04) | Quirk found |
Discovered quirks
- =VLOOKUP("a",A1:B3,5,FALSE) on LibreOffice Calc returned #VALUE!, but the documented/expected result is #REF!. Microsoft docs specify #REF! for out-of-range col_index_num; record engines' ACTUAL error code here since this is a known point of cross-engine divergence; MISMATCH vs expected: expected '#REF!', got '#VALUE!'
Executed test cases
LibreOffice Calc 24.2.7.2 (tested 2026-07-04)
| Formula | Description | Result | Expected | Verdict |
|---|---|---|---|---|
| =VLOOKUP("b",A1:B3,2,FALSE) | Exact match mode (range_lookup=FALSE) | 2 | 2 | Matched |
| =VLOOKUP(3,A1:B5,2,TRUE) | Approximate match requires ascending sorted first column | 20 | 20 3 is between 2 and 4; approx match returns the row for the largest value <= lookup (2 -> 20) |
Matched |
| =VLOOKUP("z",A1:B3,2,FALSE) | Exact match with no match -> #N/A | #N/A | #N/A | Matched |
| =VLOOKUP("a",A1:B3,5,FALSE) | col_index_num beyond the table width -> #REF! per Microsoft docs | #VALUE! | #REF! Microsoft docs specify #REF! for out-of-range col_index_num; record engines' ACTUAL error code here since this is a known point of cross-engine divergence |
Mismatch |
| =VLOOKUP("a*",A1:B3,2,FALSE) | Wildcards are honored even in exact-match mode | 1 | 1 First row matching the a* wildcard pattern is 'apple' |
Matched |
Docs & syntax
- Excel: official documentation
- Google Sheets: official documentation
- LibreOffice Calc: official documentation