vlookup not working

[fredmeister]

I have attached the file. Why is this simply vlookup not working?

[Pepe Le Mokko]

Try

Please Login or Register  to view this content.

( LEFT returns a string and your lookup value is a number)

[satputenandkumar0]

Dear fredmeister,

Which result do you want?

Regards,
Nandkumar Satpute

[:) Sixthsense :)]

Left is a Text Function and it returns text result. So comparing the Text Number with the real number in your vlookup is causing #N/A error.

using -- (Unary Operator) in front of Text result or Using Value function will convert the Text Number to Real Number.

=VLOOKUP(--LEFT(A2,4),Sheet2!A:D,4,0)

or

=VLOOKUP(VALUE(LEFT(A2,4)),Sheet2!A:D,4,0)

[crzu]

Attached is the solution.

Below is the formula.

Please Login or Register  to view this content.

You will need to add in "Value" formula. It will convert the Text to number.

If it helps you, please consider click on the star .

[satputenandkumar0]

use following formula

=VLOOKUP(VALUE(LEFT(A2,4)),Sheet2!$A$1:$D$1,4,FALSE)

Regards,
Nandkumar Satpute