# Find text within a column return true or false

#### wyzco

##### New Member
I have a column that contains a list of data in each cell. The Name followed by a "✔" if the person was present and the 7th "" indicates how much time the individual logged in.
shown below
`Jordan Charles, " ✔", "", "", "9:48", "10:07", "20", "1"`

I would like to search if a Name and ✔ is present in any cell.
When I utilize this formula to search for the name only (A18:B18) it works.
Excel Formula:
``=SUMPRODUCT(--ISNUMBER(SEARCH(A18:B18;'9h (2021-09-24)'!A:A)))=COUNTA(A18:B18)``

However when a search that includes Name and "✔" (A18:C18) which indicates if the person is present it fails.

Is there any way I could search to determine if the name is in the list and if the person was present (✔) or even to use the 7th "" which indicates if the person logged in?

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### Sufiyan97

##### Well-known Member
Try

Sheet1

Book3
ABCDEF
1JordanCharlesLogged in
2wyzcoCharlesLogged in
3JordanCharlesNot Logged in
4
5
Sheet1
Cell Formulas
RangeFormula
E1:E3E1=IF(COUNTIFS('9h (2021-09-24)'!A:A,Sheet1!A1,'9h (2021-09-24)'!C:C,Sheet1!C1)>0,"Logged in","Not Logged in")

Another sheet "9h (2021-09-24)"

Book3
ABCDE
1JordanCharles
2wyzcoCharles
3
4
5
6
9h (2021-09-24)

#### wyzco

##### New Member
My apologies, this would be what is on the data sheet consisting of the names and ✔.

Book4.xlsx
A
1Attendance for:,"9h"
2Date:, "2021-09-24", "Time:", "9:35", "Meet ID:", "sfh-szqv-eri"
3
4Names, "2021-09-24", "Email", "Comments", "Arrival time", "Last Seen", "# of Checks", "Joined", "Details"
5Amanda Anderson, " ✔", "", "", "9:40", "10:07", "28", "1"
6Michael Bartley, " ✔", "", "", "9:35", "10:07", "33", "1"
7Devon Bernard, " ✔", "", "", "9:35", "9:37", "35", "3", "9:38 (32min) [ 10:09 ]","9:35 (1min) [ 9:35 ]","9:36 (2min) [ 9:37 ]"
8Adijah Blair, " ✔", "", "", "9:37", "10:07", "31", "1", /
9Raje Britton, " ✔", "", "", "10:02", "10:09", "8", "1"
10Kimani Brown, "", "", ""
11Kyle-Anthony Brown, "", "", ""
12Khara Campbell, " ✔", "", "", "9:35", "10:07", "33", "1"
13Jordan Charles, " ✔", "", "", "9:48", "10:07", "20", "1"
14Brianna Clarkes, " ✔", "", "", "9:35", "10:07", "33", "1"
15Tishawn Clarkes, "", "", ""
16Nathonia Dower, "", "", ""
17Kebrina Edmondson, " ✔", "", "", "9:37", "9:51", "27", "3", "9:37 (12min) [ 9:48 ]","9:49 (3min) [ 9:51 ]","9:56 (12min) [ 10:07 ]"
18Jordaine Evans, "", "", ""
19Pharell Fowler, " ✔", "", "", "9:43", "10:08", "26", "1"
20Timothy Garvey, " ✔", "", "", "9:44", "10:06", "23", "1"
21Dexalie Gray, "", "", ""
22Kristoff Hacker, " ✔", "", "", "9:42", "9:49", "22", "2", "9:50 (14min) [ 10:03 ]","9:42 (8min) [ 9:49 ]"
23Oniel Haughton, "", "", ""
24Joshua Heslop, "", "", ""
25Abigail Hylton, " ✔", "", "", "9:37", "10:07", "31", "1"
26Ms. S. Jarrett, "", "", ""
27Javoy Johnson, "", "", ""
28Kingsley Keen, " ✔", "", "", "9:52", "10:07", "16", "1"
29Azahni Lemonias, " ✔", "", "", "9:49", "10:07", "19", "1"
30Rashad Martin, " ✔", "", "", "9:36", "10:07", "32", "1"
31Jhordan Maxwell, " ✔", "", "", "9:37", "10:07", "31", "1"
32Demario Miller, " ✔", "", "", "9:37", "9:37", "31", "2", "9:38 (30min) [ 10:07 ]","9:37 (1min) [ 9:37 ]"
33Ramon Moore, " ✔", "", "", "9:39", "10:07", "29", "1"
9h (2021-09-24)

The next sheet consist of formula extracting the information from the data sheet
Book4.xlsx
ABCDE
4Sept. 13, 2021Sept. 14, 2022
5First NameLast NamePresent symbol to search for
6
7JordanCharlesFALSE
8JordanCharlesTRUE
Attendance
Cell Formulas
RangeFormula
D7D7=SUMPRODUCT(--ISNUMBER(SEARCH(A7:C7,'9h (2021-09-24)'!A:A)))=COUNTA(A7:C7)
D8D8=SUMPRODUCT(--ISNUMBER(SEARCH(A8:B8,'9h (2021-09-24)'!A:A)))=COUNTA(A8:B8)

I hope that better shows what I am trying to achieve.

That is to search within 9h (2021-09-24) sheet to find a name and ✔, if all are within a cell, return True on Attendance sheet.

#### wyzco

##### New Member
Try

Sheet1

Book3
ABCDEF
1JordanCharlesLogged in
2wyzcoCharlesLogged in
3JordanCharlesNot Logged in
4
5
Sheet1
Cell Formulas
RangeFormula
E1:E3E1=IF(COUNTIFS('9h (2021-09-24)'!A:A,Sheet1!A1,'9h (2021-09-24)'!C:C,Sheet1!C1)>0,"Logged in","Not Logged in")

Another sheet "9h (2021-09-24)"

Book3
ABCDE
1JordanCharles
2wyzcoCharles
3
4
5
6
9h (2021-09-24)
I repost the using the mini-sheet to provide more clarity. my apologies and thank you for your assistance thus far. I would greatly appreciate if you can review my updated explanation to see if/how it could be resolved.

#### Sufiyan97

##### Well-known Member

Try

Book5
AB
1Attendance for:,"9h"
2Date:, "2021-09-24", "Time:", "9:35", "Meet ID:", "sfh-szqv-eri"
3
4Names, "2021-09-24", "Email", "Comments", "Arrival time", "Last Seen", "# of Checks", "Joined", "Details"
5Amanda Anderson, " ✔", "", "", "9:40", "10:07", "28", "1"
6Michael Bartley, " ✔", "", "", "9:35", "10:07", "33", "1"
7Devon Bernard, " ✔", "", "", "9:35", "9:37", "35", "3", "9:38 (32min) [ 10:09 ]","9:35 (1min) [ 9:35 ]","9:36 (2min) [ 9:37 ]"
8Adijah Blair, " ✔", "", "", "9:37", "10:07", "31", "1", /
9Raje Britton, " ✔", "", "", "10:02", "10:09", "8", "1"
10Kimani Brown, "", "", ""
11Kyle-Anthony Brown, "", "", ""
12Khara Campbell, " ✔", "", "", "9:35", "10:07", "33", "1"
13Jordan Charles, " ✔", "", "", "9:48", "10:07", "20", "1"
14Brianna Clarkes, " ✔", "", "", "9:35", "10:07", "33", "1"
15Tishawn Clarkes, "", "", ""
16Nathonia Dower, "", "", ""
17Kebrina Edmondson, " ✔", "", "", "9:37", "9:51", "27", "3", "9:37 (12min) [ 9:48 ]","9:49 (3min) [ 9:51 ]","9:56 (12min) [ 10:07 ]"
18Jordaine Evans, "", "", ""
19Pharell Fowler, " ✔", "", "", "9:43", "10:08", "26", "1"
20Timothy Garvey, " ✔", "", "", "9:44", "10:06", "23", "1"
21Dexalie Gray, "", "", ""
22Kristoff Hacker, " ✔", "", "", "9:42", "9:49", "22", "2", "9:50 (14min) [ 10:03 ]","9:42 (8min) [ 9:49 ]"
23Oniel Haughton, "", "", ""
24Joshua Heslop, "", "", ""
25Abigail Hylton, " ✔", "", "", "9:37", "10:07", "31", "1"
26Ms. S. Jarrett, "", "", ""
27Javoy Johnson, "", "", ""
28Kingsley Keen, " ✔", "", "", "9:52", "10:07", "16", "1"
29Azahni Lemonias, " ✔", "", "", "9:49", "10:07", "19", "1"
30Rashad Martin, " ✔", "", "", "9:36", "10:07", "32", "1"
31Jhordan Maxwell, " ✔", "", "", "9:37", "10:07", "31", "1"
32Demario Miller, " ✔", "", "", "9:37", "9:37", "31", "2", "9:38 (30min) [ 10:07 ]","9:37 (1min) [ 9:37 ]"
33Ramon Moore, " ✔", "", "", "9:39", "10:07", "29", "1"
34
35
36
37
9h (2021-09-24)

The next sheet consist of formula extracting the information from the data sheet

Book5
ABCDE
1
2
3
4Sept. 13, 2021
5First NameLast NamePresent symbol to search for
6
7JordanCharlesTRUE
8JordanCharlesTRUE
9AmandaAndersonTRUE
10AmandaAndersonTRUE
11MichaelBartleyTRUE
12DevonBernardTRUE
14RajeBrittonTRUE
15KimaniBrownFALSE
16KyleBrownFALSE
17KharaCampbellTRUE
18JordanCharlesTRUE
19BriannaClarkesTRUE
20TishawnClarkesFALSE
22KebrinaEdmondsonTRUE
23JordaineEvansFALSE
24PharellFowlerTRUE
25TimothyGarveyTRUE
26DexalieGrayFALSE
27KristoffHackerTRUE
28OnielHaughtonFALSE
29JoshuaHeslopFALSE
30AbigailHyltonTRUE
31MsJarrettFALSE
32JavoyJohnsonFALSE
33KingsleyKeenTRUE
34AzahniLemoniasTRUE
36JhordanMaxwellTRUE
37DemarioMillerTRUE
38RamonMooreTRUE
39
Attendance
Cell Formulas
RangeFormula
D7:D38D7=ISNUMBER(MATCH("*"&CONCATENATE(A7," ",B7,",",""," "" ✔""")&"*",'9h (2021-09-24)'!A:A,0))

#### wyzco

##### New Member
Try

Book5
AB
1Attendance for:,"9h"
2Date:, "2021-09-24", "Time:", "9:35", "Meet ID:", "sfh-szqv-eri"
3
4Names, "2021-09-24", "Email", "Comments", "Arrival time", "Last Seen", "# of Checks", "Joined", "Details"
5Amanda Anderson, " ✔", "", "", "9:40", "10:07", "28", "1"
6Michael Bartley, " ✔", "", "", "9:35", "10:07", "33", "1"
7Devon Bernard, " ✔", "", "", "9:35", "9:37", "35", "3", "9:38 (32min) [ 10:09 ]","9:35 (1min) [ 9:35 ]","9:36 (2min) [ 9:37 ]"
8Adijah Blair, " ✔", "", "", "9:37", "10:07", "31", "1", /
9Raje Britton, " ✔", "", "", "10:02", "10:09", "8", "1"
10Kimani Brown, "", "", ""
11Kyle-Anthony Brown, "", "", ""
12Khara Campbell, " ✔", "", "", "9:35", "10:07", "33", "1"
13Jordan Charles, " ✔", "", "", "9:48", "10:07", "20", "1"
14Brianna Clarkes, " ✔", "", "", "9:35", "10:07", "33", "1"
15Tishawn Clarkes, "", "", ""
16Nathonia Dower, "", "", ""
17Kebrina Edmondson, " ✔", "", "", "9:37", "9:51", "27", "3", "9:37 (12min) [ 9:48 ]","9:49 (3min) [ 9:51 ]","9:56 (12min) [ 10:07 ]"
18Jordaine Evans, "", "", ""
19Pharell Fowler, " ✔", "", "", "9:43", "10:08", "26", "1"
20Timothy Garvey, " ✔", "", "", "9:44", "10:06", "23", "1"
21Dexalie Gray, "", "", ""
22Kristoff Hacker, " ✔", "", "", "9:42", "9:49", "22", "2", "9:50 (14min) [ 10:03 ]","9:42 (8min) [ 9:49 ]"
23Oniel Haughton, "", "", ""
24Joshua Heslop, "", "", ""
25Abigail Hylton, " ✔", "", "", "9:37", "10:07", "31", "1"
26Ms. S. Jarrett, "", "", ""
27Javoy Johnson, "", "", ""
28Kingsley Keen, " ✔", "", "", "9:52", "10:07", "16", "1"
29Azahni Lemonias, " ✔", "", "", "9:49", "10:07", "19", "1"
30Rashad Martin, " ✔", "", "", "9:36", "10:07", "32", "1"
31Jhordan Maxwell, " ✔", "", "", "9:37", "10:07", "31", "1"
32Demario Miller, " ✔", "", "", "9:37", "9:37", "31", "2", "9:38 (30min) [ 10:07 ]","9:37 (1min) [ 9:37 ]"
33Ramon Moore, " ✔", "", "", "9:39", "10:07", "29", "1"
34
35
36
37
9h (2021-09-24)

The next sheet consist of formula extracting the information from the data sheet

Book5
ABCDE
1
2
3
4Sept. 13, 2021
5First NameLast NamePresent symbol to search for
6
7JordanCharlesTRUE
8JordanCharlesTRUE
9AmandaAndersonTRUE
10AmandaAndersonTRUE
11MichaelBartleyTRUE
12DevonBernardTRUE
14RajeBrittonTRUE
15KimaniBrownFALSE
16KyleBrownFALSE
17KharaCampbellTRUE
18JordanCharlesTRUE
19BriannaClarkesTRUE
20TishawnClarkesFALSE
22KebrinaEdmondsonTRUE
23JordaineEvansFALSE
24PharellFowlerTRUE
25TimothyGarveyTRUE
26DexalieGrayFALSE
27KristoffHackerTRUE
28OnielHaughtonFALSE
29JoshuaHeslopFALSE
30AbigailHyltonTRUE
31MsJarrettFALSE
32JavoyJohnsonFALSE
33KingsleyKeenTRUE
34AzahniLemoniasTRUE
36JhordanMaxwellTRUE
37DemarioMillerTRUE
38RamonMooreTRUE
39
Attendance
Cell Formulas
RangeFormula
D7:D38D7=ISNUMBER(MATCH("*"&CONCATENATE(A7," ",B7,",",""," "" ✔""")&"*",'9h (2021-09-24)'!A:A,0))

I tried using the formula
Excel Formula:
``=ISNUMBER(MATCH("*"&CONCATENATE(A7," ",B7,",",""," "" ✔""")&"*",'9h (2021-09-24)'!A:A,0))``
However when I paste it, it give a formula error, I suspect it has to do with the commas, as it suggest changing to semi-colon however it keeps failing.
Can you paste the exact formula you used or is it a issue with how I am inputting the formula?

#### Sufiyan97

##### Well-known Member
Just paste this formula in cell D7 in Attendance sheet and drag down

Excel Formula:
``=ISNUMBER(MATCH("*"&CONCATENATE(A7," ",B7,",",""," "" ✔""")&"*",'9h (2021-09-24)'!A:A,0))``

#### Fluff

##### MrExcel MVP, Moderator
If your system uses semicolons as separators you would need to use
Excel Formula:
``=ISNUMBER(MATCH("*"&CONCATENATE(A7;" ";B7;",";"";" "" ✔""")&"*";'9h (2021-09-24)'!A:A;0))``

Replies
3
Views
116
Replies
0
Views
164
Replies
2
Views
240
Replies
3
Views
360
Replies
0
Views
198

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,697
Messages
5,765,987
Members
425,320
Latest member
Galin

### 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.

### Which adblocker are you using?

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

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