Up next in 10
Comparing Lists using AND, OR, XOR and NOT Functions in Excel
420 views
Feb 7, 2025
In this tutorial, we'll break down how to use AND, OR, XOR, and NOT to analyze data effectively. Learn how to compare lists, highlight important values, and make smarter decisions with your data. Perfect for beginners and experienced users alike. Topics Covered: 00:00 How AND, OR, XOR, and NOT functions work 06:40 Comparing Two Lists 08:46 Using AND Function 10:25 Using OR Function 11:13 Using NOT Function 12:13 Using XOR Function
View Video Transcript
0:00
hi and welcome in this video we'll dive
0:03
into excel's four logical functions and
0:06
or exor and not we'll also demonstrate
0:09
how to compare two lists using these
0:11
functions to analyze data effectively
0:14
let's Dive Right In in Excel true and
0:16
false are logical values for example if
0:19
you write a logical expression like 5
0:21
greater than three Excel evaluates it
0:24
and returns true because five is indeed
0:26
greater than three similarly 5 less than
0:29
three returns false because this
0:31
condition is not met a logical
0:33
expression evaluates to either true or
0:35
false it's essentially a comparison or
0:38
condition that determines whether
0:39
something is correct or not based on the
0:41
given data or criteria logical functions
0:44
use these true and false values to
0:46
perform further calculations and
0:48
decisions now let's see how these values
0:51
interact when used within logical
0:53
functions the INF function takes one or
0:55
more conditions and checks if all of
0:57
them are true if all the conditions are
0:59
true it returns true otherwise it
1:02
returns false let's explore how The
1:04
inunction Works using some examples when
1:07
you use the formula in B5 the in
1:09
function evaluates the conditions
1:11
provided since both conditions are true
1:13
the function returns true next in B6 we
1:16
have one condition is true and the other
1:19
is false since the inunction requires
1:21
all conditions to be true it returns
1:23
false now take a look at B7 even though
1:27
two of the conditions are true the
1:29
presence of just one false causes the
1:30
function to return false instead of
1:33
directly inputting logical values like
1:35
true or false let's use practical
1:37
examples to demonstrate how the
1:38
inunction can evaluate real conditions
1:41
for example the formula in be eight
1:43
checks if both conditions five being
1:45
greater than three and four being
1:47
greater than three are true since both
1:50
are true statements the inunction
1:52
returns true in contrast formula in B9
1:55
evaluates whether five is greater than
1:57
three which is true and whether is less
2:00
than three which is false because one
2:03
condition is false the inunction returns
2:05
false these examples show how the INF
2:07
function works with both direct logical
2:09
values and real world conditional
2:11
Expressions it's a powerful tool for
2:14
testing multiple conditions at once now
2:16
let's see how the or function works the
2:18
or function checks if at least one
2:20
condition is true if any input is true
2:23
it returns true it only returns false if
2:26
all inputs are false let's see few
2:29
examples in b11 the OR function
2:32
evaluates the conditions provided since
2:34
at least one condition is true in this
2:36
case both are true the function returns
2:38
true in B12 one condition is true and
2:42
the other is false because the or
2:44
function only requires one condition to
2:46
be true it still returns true in B13
2:49
both conditions are false since there's
2:51
no True Value the or function returns
2:54
false now in b14 among the three
2:57
conditions there is at least one true so
3:00
the or function returns true instead of
3:02
directly entering logical values like
3:04
true or false let's move to practical
3:07
examples using conditional expressions
3:09
for example B15 evaluates whether five
3:12
is greater than three which is true and
3:15
whether four is greater than three also
3:17
true since at least one condition is
3:19
true the or function returns true in B16
3:23
the first condition is true but the
3:25
second condition is false because or
3:27
only needs one condition to be true the
3:30
result is true finally in B7 neither
3:33
condition is true the or function
3:35
returns false these examples show how
3:37
the or function works with both simple
3:39
logical values and real world conditions
3:42
it's a great way to check if at least
3:43
one condition is met now let's explore
3:46
how the exor function works with some
3:48
examples the exor function checks for
3:50
exclusive True Values it returns true if
3:53
the inputs have an odd number of True
3:55
Values otherwise it returns false the
3:58
xor function stands for exclusive or and
4:02
it evaluates whether an odd number of
4:04
conditions are true if the count of True
4:06
Values is odd the function returns true
4:08
if it's even the function returns false
4:11
let's look at E3 here there are two True
4:14
Values which makes the count even since
4:16
X or requires an odd number of True
4:18
Values to return true the result is
4:20
false next E4 has one true value which
4:24
is odd so the xor function returns true
4:27
now consider E5 with three True Values
4:30
the count is odd and the function
4:32
returns true in the case of E6 there are
4:35
two True Values and one false making the
4:37
count of trues even therefore xor
4:40
returns false for E7 there are four True
4:43
Values which is an even count as a
4:46
result the function returns false on the
4:48
other hand E8 has three True Values
4:51
since the count is odd the result is
4:53
true now let's see how exor works with
4:56
practical conditional Expressions
4:57
instead of directly entering l iCal
5:00
values for example in E9 the first
5:03
condition is true and the second
5:05
condition is also true since both
5:07
conditions are true the count is even
5:09
and xor returns false in E10 the first
5:13
condition is true while the second
5:15
condition is false since there is only
5:17
one true the count is odd and xor
5:20
returns true the exor function is
5:22
especially useful when you want to
5:23
determine if exactly one or an odd
5:25
number of conditions are true finally
5:28
let's talk about the not function and
5:29
how it works with some examples the not
5:31
function is straightforward it simply
5:34
reverses The Logical value of the
5:36
condition you provide if the input is
5:38
true now will return false and if the
5:40
input is false now will return true
5:43
let's start with true input in F12 since
5:46
the input is true the not function flips
5:48
it and returns false next in f-13 not
5:51
function takes false as the input and
5:53
reverses it returning true now let's
5:56
combine the not function with other
5:58
logical functions in F14 the INF
6:01
function first evaluates whether all
6:03
conditions are true since both
6:05
conditions are true and returns true the
6:07
not function then reverses this result
6:10
returning false in F15 the or function
6:13
checks if at least one condition is true
6:15
since one condition is true or returns
6:18
true the not function then flips it to
6:20
false finally in F-16 the or function
6:24
evaluates whether at least one condition
6:26
is true since both conditions are false
6:28
or returns false the KN function then
6:31
reverses it returning true the KN
6:33
function is a great way to test the
6:35
opposite of a condition or to invert the
6:37
results of other logical functions now
6:39
that you understand how these functions
6:41
work let's move on to a more practical
6:43
example comparing two lists using the
6:45
your function in one of our previous
6:47
tutorials we used the countif function
6:49
to compare two lists as you can see on
6:51
the screen the countif function counts
6:53
the number of occurrences of the value
6:55
in A2 within the range B2 to B16 if the
6:59
value exists the formula Returns the
7:02
count of occurrences otherwise it
7:04
returns zero this time instead of C if
7:08
we'll use the your function take a look
7:10
at the formula on the screen here's how
7:12
it works the or function evaluates
7:14
whether the value in A2 exists anywhere
7:16
in the range B2 to B16 as we discussed
7:19
earlier the or function needs only one
7:22
true value to return true if the value
7:24
in A2 is found in B2 to be 16 the
7:27
formula will return true otherwise it
7:30
will return false to make this even more
7:32
practical let's use conditional
7:33
formatting to highlight the cells in
7:35
column A that meet the criteria where
7:37
their values also appear in column B
7:40
first select the range in column A to
7:42
format open conditional formatting and
7:44
choose new rule in the dialogue box
7:47
enter the formula shown on the screen
7:49
first select the range in column A to
7:51
format open conditional formatting and
7:53
choose new rule in the dialogue box
7:56
enter the formula shown on the screen
7:58
this formula Compares each value in
8:00
column A to the range B2 to B 16 click
8:03
the format button to open the formatting
8:05
dialogue box here you can customize the
8:07
cell formatting for this example I'll
8:10
just change the background color to make
8:11
the matching cell stand out now any cell
8:14
in column A that has a matching value in
8:16
column B will be highlighted with the
8:18
chosen background color this makes it
8:20
easy to visually identify common items
8:22
between the two lists if you also want
8:25
to highlight items in column B that
8:26
appear in column A modify the formula as
8:29
shown on the screen and repeat the same
8:31
steps simply use the new formula in
8:33
conditional formatting and apply the
8:45
changes next let's demonstrate a
8:48
practical example using the inunction we
8:50
have a list of employees and their PCS
8:53
and our goal is to identify which PCS
8:55
need an upgrade to do this we analyze
8:58
their specifications such as as RAM dis
9:00
space and dis type our criteria for
9:03
upgrading a PC are one the ram is less
9:06
than 8 GB two the dis space is less than
9:10
512 GB three the dis type is HDD to
9:14
determine this we use the formula shown
9:16
on the screen we enter this formula in
9:18
cell F2 and copy it down to apply it to
9:21
the entire data set the formula returns
9:23
true for PCs that meet all the
9:26
conditions for an upgrade and false
9:27
otherwise to make the result more user
9:29
friendly we modify the formula to use if
9:32
condition as shown on screen now instead
9:34
of true or false the formula displays
9:37
upgraded for PCs needing an upgrade and
9:40
leaves the cell blank if no upgrade is
9:42
required to further highlight the PCS
9:44
that need upgrading we apply conditional
9:46
formatting we use the same formula in
9:49
the format dialogue box we select a
9:51
yellow background color as a result the
9:53
employee name is highlighted for PCs
9:55
that meet all the upgrade conditions if
9:58
you want to highlight the Entre higher
9:59
row that meet all the upgrade conditions
10:02
you need to use the absolute column
10:04
references let's edit the conditional
10:06
formatting Rule and modify the formula
10:08
as shown on screen the dollar signs lock
10:10
the column references so that the
10:12
formatting applies correctly to each row
10:14
instead of the first cell as a result
10:17
the entire row is highlighted for PCs
10:19
that meet all the upgrade conditions you
10:22
can clearly see that only the rows
10:23
matching all criteria are highlighted
10:26
next we'll demonstrate how the your
10:27
function works this time our goal
10:30
remains to identify which PCS need an
10:32
upgrade but the criteria are slightly
10:34
different instead of requiring all
10:36
conditions to be met a PC qualifies for
10:38
an upgrade if any condition is satisfied
10:41
to do this double click on Cell F2
10:44
replace and with or in the formula and
10:46
press enter Then double click the fill
10:48
handle to copy the updated formula down
10:51
to the entire list the formula now
10:53
returns true if at least one of the
10:55
conditions is met and false if none of
10:57
the conditions are met finally update
10:59
the conditional formatting rule edit the
11:01
existing Rule and replace and with or is
11:04
shown on the screen now the rows
11:06
matching at least one condition are
11:08
highlighted making it easy to identify
11:11
which PCS should be considered for an
11:13
upgrade next let's explore the not
11:15
function in the previous step the
11:17
conditional formatting using the or
11:19
function almost highlighted the entire
11:21
data set leaving only seven rows UNH
11:24
highlighted these rows represent PCS
11:26
that do not meet any of the upgrade
11:28
criteria now instead of highlighting PCS
11:31
that need upgrading we want to highlight
11:33
only the latest PCS as we discussed
11:36
earlier the not function negates the
11:38
result it converts true to false and
11:40
false to true we don't need to rewrite a
11:42
new formula instead we simply modify our
11:46
existing or formula by adding the not
11:48
function as shown on screen this change
11:51
reverses the logic of the formula
11:53
identifying PCS that do not need
11:55
upgrading and labeling them as latest
11:57
next we update the condition formatting
11:59
rule to include the not function once
12:02
applied the rule now highlights only the
12:04
rows for PCs that are up to date and
12:06
don't need an upgrade with this we've
12:08
successfully used the not function to
12:10
invert the criteria and focus on the
12:12
latest PCS now let's explore the XR
12:15
function using the same data set as we
12:18
discussed earlier the XR function stands
12:20
for exclusive or and determines if an
12:23
odd number of conditions are true unlike
12:25
or which returns true if any condition
12:28
is met our returns true only when one
12:31
three five or seven conditions are true
12:34
suppose we want to upgrade PCS but we're
12:36
working with a limited budget this means
12:38
we can only upgrade a PC if only one
12:41
item needs upgrading for example if the
12:43
ram is lower than 8 gabt the PC must be
12:46
upgraded and if the dis type is HDD it
12:49
also qualifies for an upgrade however if
12:52
both conditions are met the ram is less
12:54
than 8 GB and the disc is HDD we won't
12:57
be able to upgrade the PC this is where
12:59
the XR function becomes useful to
13:01
implement this we use the formula shown
13:03
on the screen you'll notice that this
13:05
formula returns upgraded for PCs where
13:08
only one of the conditions is true such
13:10
as when the ram is 8 GB and the dis type
13:13
is HDD however for PCs where both
13:16
conditions are met like when the ram is
13:17
4 GB and the dis type is HDD the formula
13:21
returns a blank cell in this way the XR
13:24
function helps us identify PCS where
13:26
only one condition is met ensur uring
13:29
that we can allocate resources
13:30
effectively within our budget finally to
13:33
make this more prominent apply or edit
13:35
the conditional formatting rule use the
13:37
XR logic in the formula to highlight
13:39
rows where only one condition is met
13:42
this visual distinction makes it easier
13:44
to identify PCS eligible for an upgrade
13:47
and that's it thank you for watching
13:49
don't forget to like share and subscribe
13:51
to the channel hit the notification Bell
13:53
to stay updated with more Excel tips and
13:55
tutorials your support helps us create
13:58
even better content
13:59
see you in the next video