Hi,

Is there a function i can use to find out if a value exists in an array?

For example, I have a long list of job titles and would like to find out
which ones have HR or Human resource somewhere in their job title.

Thanks.

Isa.

One way;

=COUNTIF(A:A,"*HR*") + COUNTIF(A:A,"*Human resource*")

Note the first term will count "Anthropologist", so you may need to
tweak the search term (e.g., "HR*" for all terms starting with HR, or
"*HR *" for all terms where HR is followed by a space character).

In article ,
Isa wrote:

Hi,

Is there a function i can use to find out if a value exists in an array?

For example, I have a long list of job titles and would like to find out
which ones have HR or Human resource somewhere in their job title.

Thanks.

Isa.

Hi,

Thanks for this but it doesn't seem to work.
What I would like is for the function to return HR if HR exists in the array.
Your suggestion does not seem to recognise when HR or Human resource exists
in the array.

Any other suggestions?

Thanks.

"JE McGimpsey" wrote:

One way;

=COUNTIF(A:A,"*HR*") + COUNTIF(A:A,"*Human resource*")

Note the first term will count "Anthropologist", so you may need to
tweak the search term (e.g., "HR*" for all terms starting with HR, or
"*HR *" for all terms where HR is followed by a space character).

In article ,
Isa wrote:

Hi,

Is there a function i can use to find out if a value exists in an array?

For example, I have a long list of job titles and would like to find out
which ones have HR or Human resource somewhere in their job title.

Thanks.

Isa.

To return HR if HR exists in the array:

=IF(COUNTIF(A:A,"*HR*"),"HR","No HR")


In article ,
Isa wrote:

Thanks for this but it doesn't seem to work.
What I would like is for the function to return HR if HR exists in the array.
Your suggestion does not seem to recognise when HR or Human resource exists
in the array.

Any other suggestions?

Thanks.

"JE McGimpsey" wrote:

One way;

=COUNTIF(A:A,"*HR*") + COUNTIF(A:A,"*Human resource*")

Hi,

I'm sorry but it's still not working.
It returns HR all the time.

Is there another function that can be used?

Thanks.

"JE McGimpsey" wrote:

To return HR if HR exists in the array:

=IF(COUNTIF(A:A,"*HR*"),"HR","No HR")


In article ,
Isa wrote:

Thanks for this but it doesn't seem to work.
What I would like is for the function to return HR if HR exists in the array.
Your suggestion does not seem to recognise when HR or Human resource exists
in the array.

Any other suggestions?

Thanks.

"JE McGimpsey" wrote:

One way;

=COUNTIF(A:A,"*HR*") + COUNTIF(A:A,"*Human resource*")

How about selecting column A
Edit|Find
What: HR

To see if the function is working ok (but that you didn't notice that it was
there).

Isa wrote:

Hi,

I'm sorry but it's still not working.
It returns HR all the time.

Is there another function that can be used?

Thanks.

"JE McGimpsey" wrote:

To return HR if HR exists in the array:

=IF(COUNTIF(A:A,"*HR*"),"HR","No HR")


In article ,
Isa wrote:

Thanks for this but it doesn't seem to work.
What I would like is for the function to return HR if HR exists in the array.
Your suggestion does not seem to recognise when HR or Human resource exists
in the array.

Any other suggestions?

Thanks.

"JE McGimpsey" wrote:

One way;

=COUNTIF(A:A,"*HR*") + COUNTIF(A:A,"*Human resource*")


--

Dave Peterson

Hi Isa,

Yes, there is a function you can use to find out if a value exists in an array. You can use the "MATCH" function in Excel to do this.

Here are the steps to follow:

1. Select the cell where you want to display the result of the search.
2. Type the following formula:
   Formula:

   =MATCH("*HR*",A1:A10,0) 

3. Replace "*HR*" with the value you want to search for. In this case, you can use "*HR*" to search for any job title that contains "HR" or "Human Resource".
4. Replace A1:A10 with the range of cells that contains the job titles you want to search through.
5. Press Enter to display the result.

The result will be the position of the first cell in the range that contains the value you are searching for. If the value is not found, the formula will return an error.

You can also use the "COUNTIF" function to count the number of cells that contain the value you are searching for. Here are the steps to follow:

1. Select the cell where you want to display the result of the search.
2. Type the following formula:
   Formula:

   =COUNTIF(A1:A10,"*HR*") 

3. Replace A1:A10 with the range of cells that contains the job titles you want to search through.
4. Press Enter to display the result.

The result will be the number of cells in the range that contain the value you are searching for.