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

Atlas87

New Member
Joined
Jan 15, 2019
Messages
10
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?

Huge thanks in advance!!!
 

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.
With the answer list in A1 and your high risk countries in B1:B5:

=SUMPRODUCT(--ISNUMBER(SEARCH($B$1:$B$5,A1)))
 
Upvote 0
with your answer cell in A1

Code:
=SUMPRODUCT(--(ISNUMBER(SEARCH({"Nigeria","Iraq","UAE","Iran","Syria"},A1))))
 
Upvote 0
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 --
 
Upvote 0
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....
 
Upvote 0
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}

Finally the sumproduct adds them:

2
 
Last edited:
Upvote 0
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}

Finally the sumproduct adds them:

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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top