VLOOKUP function does not work if data array on the other sheet

Hello! I am using VLOOKUP function which work if created on the same sheet with data array. For example, =VLOOKUP(F2;$BE$49:$BF$61;2; ). But if the data array is on another sheet like =VLOOKUP(F2;$Customers.$A$1:$B$21;2; ), then I get an error “Invalid value”. May be I am wrote formula not correct for data array from other sheet, or Calc have bugs. Would you be so kind to help me?

Hello and welcome!
Please do not ignore the fourth parameter of the VLOOKUP() function - specify it explicitly or leave out the last semicolon if you still want to use the default TRUE value for SortedRangeLookup.


So, or
=VLOOKUP(F2;$Customers.$A$1:$B$21;2; 1)

In the theory it have to be, but on the practice not. I can show you screenshot but do not know how. Both constructions =VLOOKUP(F2;$Customers.$A$1:$B$21;2) or =VLOOKUP(F2;$Customers.$A$1:$B$21;2; 1) give same result “Invalid value”

Are Customers.$A$1:$A$21 values sorted in ascending order? What values does ta contain and what value is in F2?

Do you mean “function return #N/A with message: Error: Value Not Available”?

hm - same Issue as Why gives invalid value when trying to enter something in this cell? - #2 by mikekaganski ??

99% of today’s spreadsheet users want zero as last argument.

1 Like

Yes, values sorted in ascending order.
In the F2 text values.
I create new document and try to do same like in the my document. It is work also as I make in my working document. I can not understand why it do not work.

Since you certainly look up text, you have to use the “database mode” with last argument 0.
Then you should call Tools>Options>Calc>Calculate, turn “regular expressions in formulas” OFF and “search criteria … apply to whole cells” ON. These are per-document settings.
87572.ods (19.1 KB)

I’d suggest you upload the necessary part of your file ( if @karolus is not right with his idea ). Maybe another one could understand the problem.

Thanks a lot all. Solution was found. For this cell was set Choice from list. Data was take from other sheet (range of cells). When I set Data-Check-All values. It worked.

It was five days ago, when had post that!!

1 Like