Hello,

For reference.

The LEFT function returns its value in a text string, so you need to change it to be a numeric value. Use below formula and it will work.

=VLOOKUP(LEFT(A2,5)+0,D:E,2)

Hi
As a follow up to a previous post, I made the change and it worked
The function I wrote is attached here in the file.
But, my problem now is that the Source sheet has 5-digit or 4-digit codes - and I only set the function to 5-digits, and as soon as it encounters a 4-digit code, it answers that it has not found the code.

How can this be arranged ??
Thanks so much, Leah

Hello,

Instead of setting a fixed parameter of 5 characters in the LEFT function, use LENGTH to check how many characters the lookup value has. Using previous formula as an example.

=VLOOKUP(LEFT(A2,LENGTH(D2))+0,D:E,2)

Hi,
I wrote the formula but got the result NAME #
Attached File,
Why doesn't it work for me?

Thanks so much for answering !!

The function to check for the length of a string is LEN, not LENGTH 🙂

regards

Phil

Thanks Phil,

This is one of the hazards using functions in a different language and then translating when posting.

LOL Anders.  I have the same issues when switching between VBA, PHP and JavaScript.