Can I use LEFT function in VLOOKUP?

It will work if you extend the lookup range to row 55. It's only 10 at the moment and the value you're looking up is on row 11
So using vlady's function

=VLOOKUP(--LEFT(J2,4),'Area Codes'!A1:B100,2,FALSE)

Originally Posted by greyscale

Fantastic. thank you both. Can I ask what the '--' does?

Hi,

With the data you're using the lookup table contains numbers. Even though the number you're using to look up is itself a number, when you extract the leftmost four characters the four characters are treated as a string of text. Hence a straightforward VLOOKUP() would return an error.

The '--' is what's known as a double unary operator, (no don't ask me!) and using this converts a text string to a number. You see it used a lot in SUMPRODUCT() functions. Think of it as a double minus mathematical operator which is forcing a calculation.

You could just as easily use
=VLOOKUP(VALUE(LEFT($J$2,4)),'Area Codes'!A1:B100,2,FALSE)
since the VALUE() function effectively performs the same function and converts a string to a number value.

Regards