Formula not evaluating as expected

Foo_Man_Chu

Board Regular
Joined
Jul 22, 2010
Messages
79
I have this formula:

Code:
=IF(AND(B1="HERE",LEFT(D3,7)="STC|A2:"),C3="HERE","")

It didn't return the expected result of C3 = "HERE" so I evaluated it. For some reason these two parts of the formula evaluate to TRUE:


Code:
AND(B1="HERE",LEFT(D3,7)="STC|A2:")

but overall the formula evaluates to false. I know this isn't that hard of a formula but I'm completely stumped. Can someone help me out please? Thanks in advance.
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,117
Are you testing to see if C3 = "HERE" or that's what you want it to equal under a certain condition? A formula can only return a value in C3 if it's written in C3.

=IF(AND(B1="HERE",LEFT(D3,7)="STC|A2:",C3="HERE"),"")

otherwise:

=IF(AND(B1="HERE",LEFT(D3,7)="STC|A2:"),"HERE","")
 
Last edited:

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
Formulas return a result to the cell in which they appear.

=IF(AND(B1="HERE",LEFT(D3,7)="STC|A2:"),"HERE","")
 

philwojo

Well-known Member
Joined
May 10, 2013
Messages
533
You are getting False because after the "AND" comes back as true you are doing another evaluation of C3="Here", and that is what is giving you the FALSE.

If you are trying to populate cell C3 with the word "Here" using this IF statement then you need to put the formula into cell C3, you don't need to call it out. So if you put this in C3 what do you get, is it what you want?

Code:
[COLOR=#333333]=IF(AND(B1="HERE",LEFT(D3,7)="STC|A2:"),"HERE","")[/COLOR]
 

Watch MrExcel Video

Forum statistics

Threads
1,109,311
Messages
5,527,947
Members
409,794
Latest member
ajithppajith

This Week's Hot Topics

Top