And with If statement not working

[topswim]

Below I have a formula using the AND function with my IF statement (Y35), but notice it is not performing correctly. I should of returned A35 and not ??. Trying to understand why this is not functioning correctly. Hopefully this issue will resolve my vlookup issue where, for example=vlookup(w35,A:B,{1,2},false), is only bringing back A35 instead of A35 and B35.

Y35 A35 B35 C35 w35 X35 IF(AND(X35=0,W35="N"),A35,"??")

AZGIL06E03

123

AZGIL06E03

N

0

??

 

[Fluff]

If you look at the status bar, does it mention circular references

 

[Zyndstoff]

Cells in Column X formatted as text?

 

[Zyndstoff]

If so, this should help:

IF(AND(VALUE(X35)=0,W35="N"),A35,"??")

 

[topswim]

If so, this should help:

IF(AND(VALUE(X35)=0,W35="N"),A35,"??")

Thank you. That did work Zyndstoff. I also noticed a difference in formatting of cells were different also. One is right aligned and the other left aligned for 0. Not sure why that is as it was just drag down from the cell above

0​

0

 

[topswim]

Thank you. That did work Zyndstoff. I also noticed a difference in formatting of cells were different also. One is right aligned and the other left aligned for 0. Not sure why that is as it was just drag down from the cell above

0​

0

They are all formatted as General as I did check that too. I spoke too soon, this formula does not work when a value other then 0 is in the cell per below:

N

803-756-480

#VALUE!​

 

[Fluff]

How about

=IFERROR(IF(AND(VALUE(X35)=0,W35="N"),A35,"??"),"??")

 

[Zyndstoff]

They are all formatted as General as I did check that too. I spoke too soon, this formula does not work when a value other then 0 is in the cell per below:

N

803-756-480

#VALUE!​

Well, you didn't ask for the formula to work for all numbers in the first place.
So your goal is to display the A-column value if X-column has any number in it?

 

[Zyndstoff]

Try
IF(AND(VALUE(LEFT(X35,1)>=0),W35="N"),A35,"??")

This should work whenever the first letter of X35 can be interpreted as a number as well aswhemever x35 is a number in the first place.

BTW: 803-756-480 is no value, it is a text. Texts will be aligned left, values will be aligned right in Excels "General" cells.

 

[Zyndstoff]

Okay, so I got a little glitch in the above formula. This should fix it:

=IF(ISERROR(AND(VALUE(LEFT(X35,1))>-1,W35="N")),"??",A35)

 

[Zyndstoff]

My oh my... I got it wrong again. Now this is my final shot - tested.

=IF(AND(ISERROR(VALUE(LEFT(X35,1)))=FALSE,W35="N"),A35,"??")