# Unexpected Behaviour using VLOOKUP in OpenOffice Calc

I am using `VLOOKUP` in OpenOffice Calc on Windows and trying to use a lookup table to populate some other cells.

My Lookup table ranging A1:B6 is

``````Car         4 Wheels
Florida     No
Bus         8 Wheels
Lorry       18 Wheels
Michigan    Yes
Bike        2 Wheels
``````

My cells which I plan to populate using the `VLOOKUP` data are at range E1:E6 and I am using the function

``````=VLOOKUP(D1;\$A\$1:\$B\$6;2)
``````

Applying this to the following data gives incorrect results

``````Michigan    Yes
Lorry       18 Wheels
Bike        #N/A
Car 4       Wheels
#N/A
Florida     No
``````

Bike should return 2 Wheels, but returns #N/A.

Doing some tests I have tried replacing the word Bike with another and sometimes the function gives the expected results other times it doesn't.

This function also seems to return different results depending on which order the words inyour lookup table are and which order you first initially reference them in your "population" cells.

What is the cause of this behaviour? Its very random and is causing me huge problems as I cannot progress and rely on my spreadsheet.

• Two things to try: 1. Use the match parameter of FALSE (exact match) as the fourth argument 2. Make sure all your data is trimmed (i.e. don't try comparing "Bike " with "Bike", leading/trailing spaces will mess it up) – Joe Nov 13 '16 at 23:30
• I tried it, and the results are a complete mess until exact match is specified. So what @Joe said: `=VLOOKUP(D1;\$A\$1:\$B\$6;2;FALSE)` or `=VLOOKUP(D1;\$A\$1:\$B\$6;2;0)` should solve all your problems. – Christofer Weber Nov 13 '16 at 23:46
• @Joe Supplying 0 as the forth argument labelled sort order did the trick, On your version of OO Calc is your forth parameter called "exact match"? Either way it works. – JackKrilly Nov 13 '16 at 23:46

As given in the comments you need to use the formula `=VLOOKUP(D1;\$A\$1:\$B\$6;2;0)`.
The last bit which is the `range_lookup` value (exact match in OpenOffice Calc) controls whether value needs to match exactly or not.
Set `range_lookup` to `FALSE` or `0` to require an exact match and `TRUE` or `1` to allow a non-exact match.
If `range_lookup` is `TRUE` (the default setting which is also used if `range_lookup` is not set) you need to make sure that lookup values in the first row of the table are sorted in ascending order. Otherwise, VLOOKUP may return an incorrect or unexpected value.
If `range_lookup` is `FALSE` (require exact match), values in the first column of table do not need to be sorted.