Forum Discussion
Issue Writing IF Formula
Hi Cassie
I've had a bit of a play. Try the worksheet I've attached where I have input all the Cat 1 Postcodes into a sheet called Category 1, and I have created a formula on another sheet.
The formula I have used will reference the Category 1 postcodes and produce your desired result:
=IF(COUNTIF('Category 1'!A:F,A2)>0, "Cat 1", IF(A2="", "", "N/A"))
Let me know how you go? Hopefully this helps with a workable solution for you.
Cheers
Damien
Hi Cassie
What do you classify as a category 1 location?
Is that the only requirement? If it meets that criteria list at Category 1 else it's not?
Cheers
Damien
Hi Damien,
Thank you for your reply!
I am working off a guide which classifies which postcodes are Category 1 for properties. See attached
Yes, that will be the only requirement.
If it is one of the postcodes or falls within the range then it is CAT 1, if the postcode does not fall within the range then it is Other or N/A
Hi Cassie
Are you also looking to have a single cell to write a Postcode in and to have it return a Cat 1, N/A result?
I thought I was looking at Australian Postcodes, Melbourne here!
Cheers
Damien
Hi Damien,
Thats awesome, I am in QLD! Our other office is in Melbourne.
I believe so. I would like to have a column where I manually input the postcodes of each property and the column beside it will determine whether it is CAT 1, N/A.
I was doing some more research and I am not sure if maybe a MATCH formula would be better and to put all the possible postcodes into a table on a separate sheet.
I tried this but an error message also came up.
Please help! lol
:)
Hi Cassie
I've had a bit of a play. Try the worksheet I've attached where I have input all the Cat 1 Postcodes into a sheet called Category 1, and I have created a formula on another sheet.
The formula I have used will reference the Category 1 postcodes and produce your desired result:
=IF(COUNTIF('Category 1'!A:F,A2)>0, "Cat 1", IF(A2="", "", "N/A"))
Let me know how you go? Hopefully this helps with a workable solution for you.
Cheers
Damien
What do you want the forumla to do, i'm not convinced I understand the question..
Do you have a table of postcodes and some infomation about them and you want something to sort through that table and tell you which are cat. 1 and which are something else.. or do you have the table that linked a screenshot of and you want to be able to type in a postcode somewhere and have it tell you if that postcode is cat.?
Hi Philip,
Thank you for your reply!
I am entering property addresses, each column will be broken into address, suburb, state and postcode. I want a column beside it to say whether it is a CAT 1 location or not.
I would love a formula to do this for me instead of checking the Guide each time which tells me if that postcode is CAT 1 or not. I was going to put all the possible postcodes in the formula but I am not sure if this is doable. I can create a table with all the possible postcodes, if there is some way I can make the cell check if the postcode I enter matches one in the table that would be great.
I hope that explains what I am trying to achieve a bit better. Sorry for the confusion
Your help is greatly appreciated.
Cassie
