Nov 03 2021 04:47 PM
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?
Nov 04 2021 08:17 PM
Nov 04 2021 11:46 PM
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)
Nov 04 2021 11:46 PM
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)