Ask Your Question

How can you use VLOOKUP with a string operator like LEFT() ?

asked 2016-05-17 12:14:54 +0200

mmalmeida gravatar image

See this example: Vlookup example.ods

If you have a VLOOKUP for a cell (eg =VLOOKUP(A1,C6:D8,2) ) the behaviour is expected. However, if you do =VLOOKUP(LEFT(A2,3),C6:D8,2) and LEFT(A2,3) is the exact same as A1 (4.1 in the example document, VLOOKUP will yield #N/A.

How can you make VLOOKUP work with the LEFT operator?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2016-05-17 13:39:38 +0200

m.a.riosv gravatar image

In A1 there is a number and when you use LEFT() it's converted in text, the list for search has numbers. Using VALUE(LEFT()) works.
Calc is very restrictive about the interpretation of text as numbers, It only works with direct references and VLOOKUP() searches in a list.

edit flag offensive delete link more

answered 2016-05-18 15:31:37 +0200

karolus gravatar image

[ Apart the right answer given by @m.a.riosv ]

To avoid unpredictable wrong results, don't use VLOOKUP without explizit 4th Argument 0 if the Search-array ( in Case your Example C6:C8 ) is not sorted in ascending order.
The Formula in your Example should be:




or you should sort C6:D8 on Column C

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-05-17 12:14:54 +0200

Seen: 538 times

Last updated: May 18 '16