Star if Several Criteria Match

Amy Arnold

I want to Star the cell if the center at row matches any of our centers.

The formula I am using:

=IF([Center]@row = "Carville", 1,IF([Center]@row = "Centennial", 1,IF([Center]@row = "Charleston", 1,IF([Center]@row = "Clearfield", 1,IF([Center]@row = " Earle C Clements", 1,IF([Center]@row = " Edison", 1,IF([Center]@row = " Flint Hills", 1,IF([Center]@row = " Hawaii", 1,IF([Center]@row = " Joliet", 1,IF([Center]@row = " Los Angeles", 1,IF([Center]@row = " Maui", 1,IF([Center]@row = " New Haven", 1,IF([Center]@row = " New Orleans", 1,IF([Center]@row = " San Diego", 1,IF([Center]@row = " Sierra Nevada", 1,IF([Center]@row = " Tongue Point", 1,IF([Center]@row = " Tulsa", 1,)))))))))))))))))

Paul Newcome

Technically it looks like all you need is to remove the comma after the 1 there at the end.

However, your formula may be easier to manage if you re-write it with an OR statement.

=IF(OR(Center@row = "Carville", Center@row = "Centennial", Center@row = "Charleston", Center@row = "Clearfield", Center@row = "Earle C Clements", Center@row = "Edison", Center@row = "Flint Hills", Center@row = "Hawaii", Center@row = "Joliet", Center@row = "Los Angeles", Center@row = "Maui", Center@row = "New Haven", Center@row = "New Orleans", Center@row = "San Diego", Center@row = "Sierra Nevada", Center@row = "Tongue Point", Center@row = "Tulsa"), 1)

Even easier to manage / more flexible would be creating a list of your centers on a separate sheet then using this formula that would not need to be edited if any changes to the list need to be made:

=MIN(MATCH(Center@row, {Reference Sheet Center Name Column}, 0), 1)

Paul Newcome

Technically it looks like all you need is to remove the comma after the 1 there at the end.

However, your formula may be easier to manage if you re-write it with an OR statement.

=IF(OR(Center@row = "Carville", Center@row = "Centennial", Center@row = "Charleston", Center@row = "Clearfield", Center@row = "Earle C Clements", Center@row = "Edison", Center@row = "Flint Hills", Center@row = "Hawaii", Center@row = "Joliet", Center@row = "Los Angeles", Center@row = "Maui", Center@row = "New Haven", Center@row = "New Orleans", Center@row = "San Diego", Center@row = "Sierra Nevada", Center@row = "Tongue Point", Center@row = "Tulsa"), 1)

Even easier to manage / more flexible would be creating a list of your centers on a separate sheet then using this formula that would not need to be edited if any changes to the list need to be made:

=MIN(MATCH(Center@row, {Reference Sheet Center Name Column}, 0), 1)

Amy Arnold

@Paul Newcome The formula worked perfectly. Thank you!

Paul Newcome

Happy to help. 👍️