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

[Counsellor]

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?

[JohnSUN]

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)
or
=VLOOKUP(F2;$Customers.$A$1:$B$21;2; 1)

[Counsellor]

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”

[JohnSUN]

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

@Counsellor

give same result “Invalid value”

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

[karolus]

@Counsellor

give same result “Invalid value”

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

[Villeroy]

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

Vlookup is not working fails with N/A return despite i have value

LOOKUP, MATCH and V/HLOOKUP are typical spreadsheet functions. However, most people try to mis-use a spreadsheet program as a database. 1 A 3 B 5 C 7 D =LOOKUP( 4 ; A1:B4) => B although 4 does not occur in the search column. Same result with equivalent VLOOKUP( 4 ; A1:B4 ; 2) or VLOOKUP( 4 ; A1:B4 ; 2 ; 1) or INDEX(B1:B4 ; MATCH( 4 ; A1:A4)). Spreadsheet lookups scan an ascending numeric scale and return some corresponding value that falls into a certain range of that scale. The functions…

[Counsellor]

@JohnSUN

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

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.

[Villeroy]

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)

[Wanderer]

@Counsellor

I can not understand why it do not work

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.

[Counsellor]

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.

[karolus]

@Counsellor

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!!