Format

[konew1]

Hi. I am doing a vlookup. The target cell that the vlookup refers to could contain text, could be a number representing a year (eg "2011"), could be a complete date number (say 40626 representing 24 March 2011), or could be blank.
I want to format the cell with the vlookup formula so that the result is presented meaningfully. If the format is general then when the result is a full date the presentation is visually meaningless 40626. If I format as a date then when the dta is year only (2011) it returns April 1905. I tried wrapping the vlookup inside year() but that gives errors if the target contains text.

I don't intend to use the vlookup result in any further calculations so don't mind if numeric data is converted to text

 

[Jonmo1]

Yep, inconsistent source data is a %#%# !

You're gonna have to do the vlookup twice to test, and wrap the result in a text function...
And set a threshold for the maximum year that might be entered as Year Only (say 3000)

Try

=TEXT(vlookup(...),IF(vlookup(...)>3000,"mm/dd/yyyy","####"))


And of coarse, as you said this makes the result a TEXT value, not a real number or date.

 

[konew1]

Great, thanks