Ask Your Question

VLOOKUP() returns numbers in stead of text

asked 2016-07-17 16:22:21 +0100

mschrama gravatar image

Windows 7 / LibreOffice

I switch to LibreOffice after OO kept crashing, I have a big ods file with a lot of data and a lot of VLOOKUP functions, some work well others keep giving problems. This is what happens: It returns a large number in stead of text, if I then change the lookup formula by adding or removing a $ (just for the sake of change and forcing a recalculation), the correct text is displayed. If I then 'copy' the formula by clicking the bottom right corner of the cell it messes it up again (all numbers) but if I pull it down manually a few cells and then copy it to the rest it works, but if I sort it, it is back to all numbers again. Really frustrating

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2016-07-17 23:17:48 +0100

m.a.riosv gravatar image

updated 2016-07-17 23:19:17 +0100

Without a sample file is difficult to know what is happened, some options can interfere on the result.

a) Menu/Tools/Options/LibreOffice/OpenCL, test disabling both options and restarting LibreOffice, maybe your drive is one of those non compatibles but not in the black list.

b) 4th parameter in VLOOKUP() it's relevant to find the exact value or use a sorted list for search.
LibreOffice help - VLOOKUP()

c) Menu/Tools/Options/LibreOffice calc/Calculate/Search criteria = and <> must apply to whole cells.
(And without it enable, search is significantly slower)
LibreOffice help Calc-Calculate-Match the whole cell

d) Menu/Tools/Options/LibreOffice calc/Calculate/Enable regular expressions in formulas. LibreOffice help Calc-Calculate-Regular expressions

edit flag offensive delete link more


a) solved my problem,

HenningO gravatar imageHenningO ( 2016-08-05 10:15:23 +0100 )edit

If the answer solves your question please tick the ✔.

m.a.riosv gravatar imagem.a.riosv ( 2016-08-05 11:52:58 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-07-17 16:22:21 +0100

Seen: 262 times

Last updated: Jul 17 '16