MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Check a value in a cell is anywhere in column B


Posted by Stuck and annoyed on February 12, 2002 3:18 AM

I need a function that basically reads
"If the contents in cell A1 is anywhere in column B, then type TRUE, if not type FALSE"
(this can be typed in column c?)
"If the contents in cell A2 is anywhere in column B, then type TRUE, if not type FALSE"

etc...

I know this is simple, but so am I!
Many thanks


Posted by Aladin Akyurek on February 12, 2002 3:27 AM


In C1 enter: =COUNTIF(B:B,A1)>0

===============

Posted by Saved and happier on February 12, 2002 3:39 AM

Brilliant! That has worked a treat!
Thank you for your time!

Now, is there anyway I can control the text returned ... i.e using "COUNTIF ...>0" gives a TRUE or FALSE.
Can I make it say what I want it to?
(TRUE="Loaded" FALSE="Not loaded")

Posted by Aladin Akyurek on February 12, 2002 3:50 AM

Yes. Change the formula first slightly:

In C1 enter: =(COUNTIF(B:B,A1)>0)+0

Then custom format (via Format|Cells) C1 as

[=1]"Loaded";[=0]"Not Loaded";General

============= :


Posted by thank you on February 12, 2002 3:53 AM

Thank you so much for your time ... this has saved me several hours worth of manual work!

: Brilliant! That has worked a treat!