Conditional Formula with Text

For each row, evaluate Column E and then check Column G for contents

If E is Greater than 85 AND G contains "Express" I want to highlight the Cell in E.

My formula:
=IF(AND(INDIRECT("E"&ROW())>=85,(FIND("Express",INDIRECT("G"&ROW())),"TRUE","False"))

When I try this formula in Conditional format I get wonderfully descriptive window dialog: "The formula you typed contains an error"

Tried this in a regular cell and stepped through it, says there is an error and suggests THIS formula:
=IF(AND(INDIRECT("E"&ROW())>=85,(FIND("Express",INDIRECT("G"&ROW())))),"TRUE","False")

However, this returns #Value if there any value in G that does NOT contain Express
but functions correctly otherwise.

I am sure that it is just a need to set the false to a value but it returns a 1 for Express and nothing for anything else.

I tried adding "true","False" throughout the formula but have been not successful in getting a valid formula.

Any help?

Why are you using INDIRECT in the formula? It seems you're just checking cells on the same row, which you can do with conditional formatting without needing to use INDIRECT.

If, say, the range you're formatting is A2:A100 then you'd use the formula:

=AND(E2>=85,ISNUMBER(FIND("Express",G2)))

Conditional formatting will automatically adjust the row numbers being checked for every cell in your range.

BTW, you get the #VALUE error because if the string "Express" isn't found then FIND doesn't return a FALSE, it returns a #VALUE error, but you can trap this using ISNUMBER.

Oh, and if you conditional formatting is being applied to multiple columns then you'd probably want to use the formula:

=AND($E2>=85,ISNUMBER(FIND("Express",$G2)))

But still write it as if it applies only to the first row of the range you're conditionally formatting.

K m:

The OP says "greater than" in the text of their post, but is using >= in their formula.

Your formula isn't what they require because they specify that column G (not column F) contains "Express", not is equal to "Express"

You are correct about my >= 85, that was a mistake it should just be >84.

As for why I am using Indirect - to reduce the # of formulas and keep this in the Conditional Formatting.
I am not doing this in the row, but from the Conditional Formating area and did not want to add a formula for each row.

I tried the suggested =IF(AND(INDIRECT("E"&ROW())>=85,(isnumber(FIND("Express",INDIRECT("G"&ROW()))),"TRUE","False"))
but that did not work in the conditional OR in a cell check.

You don't need to do the formula for each row - we understand that it's conditional formatting you're using.

Select the whole range you want to format, choose to use a formula and enter the formula:

=AND($E2>84,ISNUMBER(FIND("Express",$G2)))

(Assuming that row 2 is the first row you are conditionally formatting). Conditional formatting will sort the rest out, there's no need to do it for every row.

Originally Posted by twiggywales

if you don't care about capitalisation then you need to alter FIND to SEARCH)

Both will cause problems, because they both return #VALUE if the text isn't found.

E G Value I want returned.

10 Express FALSE
90 Express TRUE
200 Standard FALSE
20 Standard FALSE

I tried the =AND($E2>84,ISNUMBER(FIND("Express",$G2))) formula - it did not work as expected.

However the suggested by Twiggywals DOES:
=AND($E1>84,FIND("Express",$G1,1))

Thank you for the quick response and teaching me that you DONT need indirect in conditional (my misunderstanding) and that excel is smart enough to correctly apply this out to the row!

Thank you, thank you, thank you!

The only reason my formula doesn't work as expected is that your data obviously starts on row 1, and not row 2, but I've told you in three times in this thread that it has to be written as if it applies to the first row you're formatting.

It's why people should always post example workbooks - it makes it much more likely they'll get a working solution

Actually, it starts on Row 5 and the columns were not as posted, I used those values to simplify my issue.
I actually updated both formulas according to the sheet I was currently working on (1 workbook, multiple sheets, different columns and starting rows on each sheet) two with detail, lastly a summary sheet.

I was trying not to muddy the water with extraneous information that was not pertinent to the issue and would likely have sidetracked people down roads that were not the issue.

I am new to the forum, I am not totally new to excel. I did not know you could upload excel workbooks on this forum so I was trying to give detail to the issue. With all the virus and issue that can cross with them I was trying to keep it simple.

That being said, when I have an issue here again, I will upload a sample.

Thanks