CONTAINS or HAS formula extension

JMOHI

Hi community,

I am looking to extend either a CONTAINS or HAS formula to pick up a dropdown value from one column and assign it a numerical value in a separate column. Can anyone assist in what is the most effective way to do so?

The intention is when any value from the "Suburb Population" column is selected via dropdown it will assign the applicable score in the "Population Ranking" column. Can anyone assist?

Rankings.JPG

=IF(CONTAINS("20K +", [Suburb Population]@row), 10) or

=IF(HAS("20K +", [Suburb Population]@row), 10)

Bassam Khalil

Hi @JMOHI

Hope you are fine, i advise you to use HAS function in your exercise because it's Search for an exact match of a value but you need to mention the exact value in the search criteria for this function for example:

  =IF(HAS("18K-20K", [Suburb Population]@row), 9)

i don't prefer to use CONTAINS Function here becuse it's Used within another function to search for a character or string.

JMOHI

Thanks @Bassam Khalil well noted, how would I extend this formula to produce a value for all "Suburb Population" values?

Bassam Khalil

@JMOHI

If you can share me on a sample copy of your sheet as an admin I can create the exact formula for you then you can copy it to your original sheet.

ddutta

HAS({Impacted_area}, HAS({Impacted_Area}, "Test") The impacted_area is a reference from a different sheet.

This is part of a COUNTIFS and this is one of the criterion.