# Checking multiple cells to see if they contain any of a list of choices

#### Atlas87

##### New Member
Good morning guys...

I have been racking my (less-than-ample) brain on this one for a couple of days... please help...

If i have a questionnaire which contains something like "What countries do you do business with?" and it yields a single cell with all their answers delimited by a comma, is there a way i can check to see which/how many of those answers are on another list i have? I know that i can "text to columns" to split them but i don.t know how look through that list against another set of multiple criteria.

Essentially, a horizontal or vertical group of cells containing countries which i want to check against another high risk country list.

To illustrate....

Their answer list; UK, USA, Nigeria, Iraq, France Germany

My high risk country list; Nigeria Iraq, UAE, iran, Syria

Question; Is there a single formula to check how many of my high risk countries appear in the respondents answer list?

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### steve the fish

##### Well-known Member
With the answer list in A1 and your high risk countries in B1:B5:

=SUMPRODUCT(--ISNUMBER(SEARCH(\$B\$1:\$B\$5,A1)))

#### AlanY

##### Well-known Member

Code:
``=SUMPRODUCT(--(ISNUMBER(SEARCH({"Nigeria","Iraq","UAE","Iran","Syria"},A1))))``

#### Atlas87

##### New Member
This is outrageous - Steve i could kiss you...but i wont, and not because you are a fish.

Two questions though....

1. It yielded an answer of 1 where i know from manual checking it should have said 2. Any thoughts?

2. For my own interest/learning....What is the significance of the --

#### Atlas87

##### New Member

This is outrageous - Steve i could kiss you...but i wont, and not because you are a fish.

Two questions though....

1. It yielded an answer of 1 where i know from manual checking it should have said 2. Any thoughts?

2. For my own interest/learning....What is the significance of the --

EDIT: I need to put the comas in per the exact nature of the list for the right answer....

#### steve the fish

##### Well-known Member
If we split the formula out it starts by checking the position of the contents of B1 within A1. Then does the same for B2 within A1 etc. This creates an array eg:

{10;20;#value !;#value !;#value !}

isnumber then converts this to:

{true;true;false;false;false}

the double negative -- will mean excel will try to convert the text to a number. In this case it can so it becomes:

{1;1;0;0;0}

2

Last edited:

#### Atlas87

##### New Member
If we split the formula out it starts by checking the position of the contents of B1 within A1. Then does the same for B2 within A1 etc. This creates an array eg:

{10;20;#value !;#value !;#value !}

isnumber then converts this to:

{true;true;false;false;false}

the double negative -- will mean excel will try to convert the text to a number. In this case it can so it becomes:

{1;1;0;0;0}

2

OK - slight problem with this again...maybe i'm doing something wrong....i'm getting bizzarely high (and wrong) answers...

for cells with only 5 entries, the formula is returning 13.

for some cells with three entries it is returning 23

theres even a cell with one country in it which has returned 27.

#### steve the fish

##### Well-known Member
You are doing something wrong. Its not possible for the formula i provided to produce any number higher than that the number of cells in the SEARCH find_text. In the case of the provided formula its a max of 5 (B1:B5 being 5 cells). Id imagine you have done something with the ISNUMBER part.

Replies
9
Views
118
Replies
0
Views
212
Replies
19
Views
824
Replies
6
Views
226
Replies
3
Views
280

1,109,437
Messages
5,528,762
Members
409,834
Latest member
vexceled

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...