Forum Discussion

Copper Contributor

Nov 03, 2021

Solved

VLOOK UP Error

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 "...

excel

Formulas and Functions

office 365

Riny_van_Eekelen
Nov 04, 2021
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)

ImalkaJ

Copper Contributor

Nov 04, 2021

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?

Riny_van_Eekelen

Platinum Contributor

Nov 04, 2021

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)