Excel 2007 : Using Formula result as TEXT for other formulas.

Perhaps you are saying you want something like:

=IF(INPUT!$P24=0;"";VLOOKUP(INPUT!$P2;'anothersheet'!$A:$B;2;0))

In terms of how you coerce a number to a string, use &"", eg: =(1*2)&""

Hi

Will this help? Please open the files i uploaded.

The first is the formula to get the name of the tecnician.
The second finds the ID based on the name.

The name is exactly the same but it cant find the ID, wish is in the first collumn i use in the second print screen.

This must be this way because... well maybe not.
IŽll try doing in reverse.

Images are of limited use in truth.

We can tell from the Image that the VLOOKUP is failing to find a match but we have no idea if the names are actually listed in INPUT!O:O

(note: your table array contains 2 coumns [O:P] yet presently you're returning column 1 - this doesn't affect the current Error but is something you should be aware of)

by all means upload your file - I am going off line for a while but rest assured others will be able to resolve for you

Bear in mind I can't see the file you're working on at this moment but...

The VLOOKUP you had - which was A:E would always return #N/A irrespective of whether or not you coerced the numbers stored as strings in DB!E:E simply because the criteria does not exist in DB!A:A

What you're describing is certainly not a bug - it's simply that if a cell is format as General and you paste in values stored as text they will remain as text.
If you re-enter the values manually XL will see they are numbers are given the General format will treat as a number - what was stored in the cell previously is of no concern.

Coercing the values would only make your formulae work if you're using B3 as the criteria and NOT B3&"" (ie you're searching for the number now rather than the string)

If you want to coerce the values in DB!E:E you can do them all at once by highlighting the column -> Data -> Text to Columns -> click Finish
(you can also select a blank cell, highlight all the number entries and run Paste Special -> Operation: Add)