X and Y are not correlated, but X is significant predictor of Y in multiple regression. What does it mean? X and Y are not correlated (-.01); however, when I place X in a multiple regression predicting Y, alongside three (A, B, C) other (related) variables, X and two other variables (A, B) are significant predictors of Y. Note that the two other (A, B) variables are significantly correlated with Y outside of the regression. 
How should I interpret these findings? X predicts unique variance in Y, but since these are not correlated (Pearson), it is somehow difficult to interpret.
I know of opposite cases (i.e., two variables are correlated but regression is not significant) and those are relatively simpler to understand from a theoretical and statistical perspective. Note that some of the predictors are quite correlated (e.g., .70) but not to the extent that I would expect substantial multicollinearity. Maybe I am mistaken, though.  
NOTE: I asked this question previously and it was closed. The rational was that this question is redundant with the question "How can a regression be significant yet all predictors be non-significant?". Perhaps I do not understand the other question, but I believe these are entirely separate questions, both mathematically and theoretically. My question is entirely independent from if "a regression is significant". Furthermore, several predictors are significant, while the other question entails variables not being significant, so I don't see the overlap. If these questions are redundant for reasons I do not understand, please insert a comment prior to closing this question. Also, I was hoping to message the moderator who closed the other question to avoid identical questions, but I couldn't find an option to do so. 
 A: I agree with the previous answer but hope I can contribute by giving more details.
The correlation coefficient is just measuring the linear dependence between $X$ and $Y$ and it's not controlling for the fact that other variables might be involved in the relationship as well. In fact the correlation coefficient equals the slope parameter of the following regression scaled by $x$ and $y$ standard deviations :
$Y = a + \beta x + u$
where $\hat \rho_{yx} = \hat \beta  \hat\sigma_x/\hat\sigma_y$
But what happens if $Y$ is generated by other variables as well, thus the real model is something like:
$Y = a + \beta x + \sum_j\alpha_jz_j + u$
Under this real model, it becomes obvious that estimating the first one (only with x) will yield a biased  $\beta$ estimate as that model is omitting the $z_j$ regressors(this implies that $\rho$ is also biased !). So your results are in line with the fact that the omitted variables are relevant. To deal with this issue , theory on correlation analysis provides the partial correlation coefficient (I'm sure you will find references on this) which basically calculates $\rho_{xy|z}$ from the latter estimating equation that controls for $z_j$.  
A: Causal theory offers another explanation for how two variables could be unconditionally independent yet conditionally dependent. I am not an expert on causal theory and am grateful for any criticism that will correct any misguidance below.
To illustrate, I will use directed acyclic graphs (DAG). In these graphs, edges ($-$) between variables represent direct causal relationships. Arrowheads ($\leftarrow$ or $\rightarrow$) indicate the direction of causal relationships. Thus $A \rightarrow B$ infers that $A$ directly causes $B$, and $A \leftarrow B$ infers that $A$ is directly caused by $B$. $A \rightarrow B \rightarrow C$ is a causal path that infers that $A$ indirectly causes $C$ through $B$. For simplicity, assume all causal relationships are linear.
First, consider a simple example of confounder bias:

Here, a simple bivariable regression will suggest a dependence between $X$ and $Y$. However, there is no direct causal relationship between $X$ and $Y$. Instead, both are directly caused by $Z$, and in the simple bivariable regression, observing $Z$ induces a dependency between $X$ and $Y$, resulting in bias by confounding. However, a multivariable regression conditioning on $Z$ will remove the bias and suggest no dependence between $X$ and $Y$.
Second, consider an example of collider bias (also known as Berkson's bias or berksonian bias, of which selection bias is a special type):

Here, a simple bivariable regression will suggest no dependence between $X$ and $Y$. This agrees with the DAG, which infers no direct causal relationship between $X$ and $Y$. However, a multivariable regression conditioning on $Z$ will induce a dependence between $X$ and $Y$, suggesting that a direct causal relationship between the two variables may exist when in fact, none exist. The inclusion of $Z$ in the multivariable regression results in collider bias.
Third, consider an example of incidental cancellation:

Let us assume that $\alpha$, $\beta$, and $\gamma$ are path coefficients and that $\beta = -\alpha\gamma$. A simple bivariable regression will suggest no dependence between $X$ and $Y$. Although $X$ is, in fact, a direct cause of $Y$, the confounding effect of $Z$ on $X$ and $Y$ incidentally cancels out the effect of $X$ on $Y$. A multivariable regression conditioning on $Z$ will remove the confounding effect of $Z$ on $X$ and $Y$, allowing for the estimation of the direct effect of $X$ on $Y$, assuming the DAG of the causal model is correct.
To summarize:
Confounder example: $X$ and $Y$ are dependent in bivariable regression and independent in multivariable regression conditioning on confounder $Z$.
Collider example: $X$ and $Y$ are independent in bivariable regression and dependent in multivariable regression conditioning on collider $Z$.
Incidental cancellation example: $X$ and $Y$ are independent in bivariable regression and dependent in multivariable regression conditioning on confounder $Z$.
Discussion:
The results of your analysis are not compatible with the confounder example but are compatible with both the collider example and the incidental cancellation example. Thus, a potential explanation is that you have incorrectly conditioned on a collider variable in your multivariable regression and have induced an association between $X$ and $Y$ even though $X$ is not a cause of $Y$ and $Y$ is not a cause of $X$. Alternatively, you might have correctly conditioned on a confounder in your multivariable regression that was incidentally cancelling out the true effect of $X$ on $Y$ in your bivariable regression.
I find using background knowledge to construct causal models helpful when considering which variables to include in statistical models. For example, if previous high-quality randomized studies concluded that $X$ causes $Z$ and $Y$ causes $Z$, I could make a strong assumption that $Z$ is a collider of $X$ and $Y$ and not condition upon it in a statistical model. However, if I merely had an intuition that $X$ causes $Z$, and $Y$ causes $Z$, but no strong scientific evidence to support my intuition, I could only make a weak assumption that $Z$ is a collider of $X$ and $Y$, as human intuition has a history of being misguided. Subsequently, I would be skeptical of inferring causal relationships between $X$ and $Y$ without further investigating their causal relationships with $Z$. In lieu of or in addition to background knowledge, there are also algorithms designed to infer causal models from the data using a series of tests of association (e.g. PC algorithm and FCI algorithm, see TETRAD for Java implementation, PCalg for R implementation). These algorithms are very interesting, but I would not recommend relying on them without a strong understanding of the power and limitations of causal calculus and causal models in causal theory.
Conclusion:
Contemplation of causal models does not excuse the investigator from addressing the statistical considerations discussed in other answers here. However, I feel that causal models can nevertheless provide a helpful framework when thinking of potential explanations for observed statistical dependence and independence in statistical models, especially when visualizing potential confounders and colliders.
Further reading:
Gelman, Andrew. 2011. "Causality and Statistical Learning." Am. J. Sociology 117 (3) (November): 955–966.
Greenland, S, J Pearl, and J M Robins. 1999. “Causal Diagrams for Epidemiologic Research.” Epidemiology (Cambridge, Mass.) 10 (1) (January): 37–48.
Greenland, Sander. 2003. “Quantifying Biases in Causal Models: Classical Confounding Vs Collider-Stratification Bias.” Epidemiology 14 (3) (May 1): 300–306.
Pearl, Judea. 1998. Why There Is No Statistical Test For Confounding, Why Many Think There Is, And Why They Are Almost Right.
Pearl, Judea. 2009. Causality: Models, Reasoning and Inference. 2nd ed. Cambridge University Press.
Spirtes, Peter, Clark Glymour, and Richard Scheines. 2001. Causation, Prediction, and Search, Second Edition. A Bradford Book.
Update: Judea Pearl discusses the theory of causal inference and the need to incorporate causal inference into introductory statistics courses in the November 2012 edition of Amstat News. His Turing Award Lecture, entitled "The mechanization of causal inference: A 'mini' Turing Test and beyond" is also of interest.
A: I think @jthetzel's approach is the right one (+1).  In order to interpret these results you will have to think about / have some theory of why the relationships manifest as they do.  That is, you will need to think about the pattern of causal relationships that underlies your data.  You need to recognize that, as @jthetzel points out, your results are consistent with several different data generating processes.  I don't think that any amount of additional statistical tests on the same dataset will allow you to distinguish amongst those possibilities (although further experiments certainly could).  So thinking hard about what's known about the topic is vital here.  
I want to point out another possible underlying situation that could generate results like yours: Suppression.  This is more difficult to illustrate using the arrow diagrams, but if I can augment them slightly, we could think of it like this:  

What's important about this situation is that the $\text{Other Variable}$ is made up of two parts, an unrelated ($\text{U}$) part, and a related ($\text{R}$) part.  The $\text{Suppressor}$ will be uncorrelated with $\text{Y}$, but may very well be 'significant' in a multiple regression model.  Furthermore, the $\text{Other Variable}$ may or may not be 'significantly' correlated with the $\text{Suppressor}$ or $\text{Y}$ on its own.  Moreover, your variable X could be playing the role of either the $\text{Suppressor}$ or the $\text{Other Variable}$ in this situation (and thus, again, you need to think about what the underlying pattern might be based on your knowledge of the area).  
I don't know if you can read R code, but here's an example I worked up.  (This particular example fits better with X playing the role of the $\text{Suppressor}$, but both are not 'significantly' correlated with $\text{Y}$; it should be possible to get the correlation between the $\text{Other Variable}$ and $\text{Y}$ close to 0 and match the other descriptives with just the right settings.)  
set.seed(888)                            # for reproducibility

S  =         rnorm(60, mean=0, sd=1.0)   # the Suppressor is normally distributed
U  = 1.1*S + rnorm(60, mean=0, sd=0.1)   # U (unrelated) is Suppressor plus error
R  =         rnorm(60, mean=0, sd=1.0)   # related part; normally distributed
OV = U + R                               # the Other Variable is U plus R
Y  = R +     rnorm(60, mean=0, sd=2)     # Y is R plus error

cor.test(S, Y)                           # Suppressor uncorrelated w/ Y
# t = 0.0283, df = 58, p-value = 0.9775
# cor 0.003721616 

cor.test(S, OV)                          # Suppressor correlated w/ Other Variable
# t = 8.655, df = 58, p-value = 4.939e-12
# cor 0.7507423

cor.test(OV,Y)                           # Other Var not significantly cor w/ Y
# t = 1.954, df = 58, p-value = 0.05553
# cor 0.2485251

summary(lm(Y~OV+S))                      # both Suppressor & Other Var sig in mult reg
# Coefficients:
#              Estimate Std. Error t value Pr(>|t|)   
# (Intercept)   0.2752     0.2396   1.148  0.25557   
# OV            0.7232     0.2390   3.026  0.00372 **
# S            -0.7690     0.3415  -2.251  0.02823 * 

My point here isn't that this situation is the one that underlies your data.  I don't know if this is more or less likely than the options @jthetzel suggests.  I only offer this as more food for thought.  To interpret your current results, you need to think about these possibilities and decide what makes the most sense.  To confirm your choice, careful experimentation will be needed.
