Ask Your Question

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

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 close merge delete

## 2 Answers

Sort by » oldest newest most voted 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.

more [ 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:

=VLOOKUP(A1,C6:D8,2,0)


or

=VLOOKUP(VALUE(LEFT(A2,3)),C6:D8,2,0)


or you should sort C6:D8 on Column C

more

## Stats

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

Seen: 416 times

Last updated: May 18 '16