0

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.

  • 1
    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
1

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.

Where your problem originates from:
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.