# Microsoft Office Application Help - Excel Help forum > For Other Platforms(Mac, Google Docs, Mobile OS etc) >  >  vlookup in Libreoffice returning Err:502 - formats issue? Excel2016 shows #N/A or #REF

## firepitz

In the enclosed, I want the profit sheet column B to show the column C value from refsources. It could also reference column A from refsources but I thought Vlookup values need to be to the right of the column array you are searching for a match within. However it shows Err:502 currently in Libreoffice. In Excel2016 shows #N/A or #REF! If you change the index to 1 it shows the last name however. 

I was working on this file in Libreoffice. The data is originally pulled from a 3rd party software and my suspicion is the issue is the way the data imports is not properly formatted (i.e. text instead of strings instead numbers or similar). I suspect some trick that I cant figure out needs to be employed to ensure all the data is in proper formats....

----------


## firepitz

forgot attachment!

----------


## MrShorty

I am assuming the VLOOKUP() works the same in LibreOffice as it does in Excel (most spreadsheet programs try to have their functions work like Excel's functions when they have the same function).

The N/A errors indicate that the lookup value is not found in refsources column B. A quick manual search (assuming data is sorted so I didn't manual search the entire column) suggests that these names are indeed not present in column B. If you believe differently, indicate where those names are in the lookup table.

The REF errors indicate that you have asked Excel to return a value from outside of the lookup table. The lookup table argument is only column B. The third argument (2) is asking Excel to return a value from the 2nd column of a 1 column reference, which causes a problem. By changing the lookup table to B:C (instead of B:B), the REF errors are changed to the desired return value from column C.

Does that help?

----------


## AliGW

New thread opened in the commercial services section, so I am closing this one.

----------

