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!!!
 

Some videos you may like

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
Joined
Oct 20, 2009
Messages
8,111
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 30, 2014
Messages
4,191
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
with your answer cell in A1

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

Atlas87

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

ADVERTISEMENT

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
Joined
Oct 20, 2009
Messages
8,111
Office Version
  1. 365
Platform
  1. Windows
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:

Atlas87

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

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,111
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top