VLOOK UP Error

[ImalkaJ]

Hi there,

I have recreated the issue I face in the simplest format possible . Excel is attached here. 

Notes:
On the output sheet - Column A is driven by a formula too. Data format is set to "General". Column A on both sheets are set to General on the original file too. 

Required output : I need the name on column B" to read as "Cloud" . 

In my original file I converted the code to number in both sheets , it still does not gives the result I wanted. I know on this sheet when the number is converted to number Vlook up works. But on the original file it's not the case. 

My workaround: Copying the data from "Data sheet" on column A and paste it, But it's not feasible when there are 1000 of data. 

How can I correct this error?

[#2]

@ImalkaJ You did not attach the file. Please try again.

[#3]

@Riny_van_Eekelen 

[#4]

I attached the file now. Thanks for looking into this.

[#5]

@ImalkaJ 

cheers

[#6]

@Yea_So Yes those are two data types and I converted those to the same type on Output sheet. The file I attached does not have that problem. It convets to the number just fine.
But on my original file even if I convert the value to number it still does not. The functionality I use is "Number" on home. Is there any other method to convert this?

[#7]

@ImalkaJ 

try selecting the cells and clear all formats

[#8 · Accepted solution]

@ImalkaJ Formatting cells as Number as you describe (i.e. on the Home ribbon) does not transform the underlying texts (that may look like numbers) into real numbers. For that you can use Text-to-columns.

Alternatively, if you are nor sure about the real type of a "number", use the following formula on the Output sheet:

=VLOOKUP(--A2,'Data sheet'!A:J,10,FALSE)

The double dash in front of A2 will force Excel to interpret what's in A2 as a real number.

You could also use *1 after A2. That has the same effect.

=VLOOKUP(A2*1,'Data sheet'!A:J,10,FALSE)