Forum Discussion

Copper Contributor

Jul 29, 2025

Solved

Conditional Formatting with formula

Hi, I hope everyone is doing well. =AND(LEFT($A1,2)="2G", $C1="KIMBERLEY") Can someone please help me with this formula, it keeps giving me an error and I do not know whats wrong with it. I want t...

excel

Formulas and Functions

m_tarler
Jul 30, 2025
depending on where you live / local setting you may need to use ; instead of , so it might be:
=AND(LEFT($A1;2)="2G"; $C1="KIMBERLEY")
as for 'hard coding' you can use a table on this or another sheet to help define the possible combinations and something like:
=AND(ISNUMBER(XMATCH(LEFT($A1;2); TABLE1[codes])); ISNUMBER(XMATCH($C1; TABLE1[cities])))

OlufemiO

Brass Contributor

Jul 30, 2025

Help with Conditional Formatting Formula – Highlight 2G IDs with Specific Town

Hello,

I am trying to apply Conditional Formatting to highlight values in Column A (IDs) only when the following two conditions are both true:

The ID in Column A starts with "2G"
The corresponding Town in Column C is "KIMBERLEY"

Formula used:
=AND(LEFT($A2,2)="2G", $C2="KIMBERLEY")

Setup:

I selected the range: A2:A11
Went to: Conditional Formatting → New Rule → Use a formula to determine which cells to format
Pasted the formula above
Applied my preferred formatting (e.g., green fill)

Expected Result:
Only the IDs in Column A that start with 2G and have "KIMBERLEY" in Column C should be highlighted.

Question:
Can anyone confirm if this is the best formula for this scenario?
Are there any improvements or best practices I should consider?

Thanks so much!