Validating Australian Post Code Range - Conditional Formatting Formula

[louisepr]

Hi
I am looking to validate Australian Postal Codes (column I) based on the state value in column H with conditional formatting if possible
The postcode of each state must start with the number as listed below. So, If State = SA and Postcode does not start with 5, highlight the cell. Or If State = QLD and Postcode does not start with 4, highlight the cell etc.
SA 5,
QLD 4
NSW 2
VIC 3
WA 6
TAS 7
ACT 2
NT 0

Thanks in advance

 

[jtakw]

Hi,

One way.
Create a State and Postal Code table as in my sample (Must be in Alpha Order),
Then use the formula for Conditional Formatting:

Book3.xlsx
	H	I	J	K	L	M
1	State	Postcode			ACT	2
2					NSW	2
3	SA	3095	TRUE		NT	0
4	SA	5114	FALSE		QLD	4
5	SA	5034	FALSE		SA	5
6	VIC	5114	TRUE		TAS	7
7	VIC	3095	FALSE		VIC	3
8	VIC	3029	FALSE		WA	6
Sheet731

Cell Formulas
Range	Formula
J3:J8	J3	=LEFT(I3)+0<>LOOKUP(H3,L$1:L$8,M$1:M$8)

If you don't want to have a State/Postal Code table in your sheet, we can Hardcode the Table within the formula, let me know.

 

[louisepr]

Perfect. Thank you

 

[jtakw]

You're welcome, thanks for the feedback.