Excel - Check a column for values found in another column

What?
This is an article to remind me how to search a column in an Excel file for values found in another column (in this example, on another worksheet in the same workbook).

How?
So for demonstration purposes, I'm using a new Excel file with two worksheets called "Sheet1" and "Sheet2" respectively.

Sheet1 contains the following:
copyraw
A               B 
--------------- ---------------
seize
spell
fence
total
thank
fight
noise
terms
thigh
tasty
light
swarm
shelf
allow
unity
press
board
wreck
shake
decay
  1.  A               B 
  2.  --------------- --------------- 
  3.  seize 
  4.  spell 
  5.  fence 
  6.  total 
  7.  thank 
  8.  fight 
  9.  noise 
  10.  terms 
  11.  thigh 
  12.  tasty 
  13.  light 
  14.  swarm 
  15.  shelf 
  16.  allow 
  17.  unity 
  18.  press 
  19.  board 
  20.  wreck 
  21.  shake 
  22.  decay 
Sheet2 contains the following::
copyraw
A               
--------------- 
warm
noise
term
wreck
  1.  A 
  2.  --------------- 
  3.  warm 
  4.  noise 
  5.  term 
  6.  wreck 

The formula to paste in column B of Sheet1 is as follows:
copyraw
=SUMPRODUCT(--ISNUMBER(SEARCH(Sheet2!$A$1:$A$4,Sheet1!A1)))>0
  1.  =SUMPRODUCT(--ISNUMBER(SEARCH(Sheet2!$A$1:$A$4,Sheet1!A1)))>0 
    Where:
  • SumProduct( is a function to multiply two ranges together (must be the same size) and return the total of these results.
  • -- is a double hyphen/dash which converts a boolean true to 1 and boolean false to 0.
  • IsNumber( is a function returning a boolean. Returns false if a cell value has been formatted to a text type. Returns true if the cell value is a number AND the cell data type is numeric.
  • Search( is a case-insensitive search with the first parameter being the text to search for (can be a range) and the second the string to search (in this case a single cell value).
  • Sheet2!$A$1:$A$4 is the range of words to find from Sheet2. Note that there is a dollar in front of both column name (eg. "A") and the row number (eg. "1") so that these don't automatically change when you copy the formula to other parts of the spreadsheet (ie. not relative).
  • Sheet1!A1 is the cell to search for those words. Note how I don't add a dollar, this is so that the formula will apply to each row and increment based on how you copy the formula to other cells.

You should end up with something like this (I have added some conditional formatting which colors the cells red or green for demonstration purposes):
Screenshot Result of Formula
If you understand this article, you may have noticed that the range of words to find from Sheet2 in this screenshot are from the cells B1:B4... This is because I already had values in A1:A4 and wanted to test against a set of different words.
Category: Excel :: Article: 667

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.