Comparing the first 2 characters of a coloumn to a string
 

Hi. I'm looking for a way to compare the first 2 charcters of a range of
cells in a column to a string and if any of the cells contain that string to
drop an "ok" result into the formulas cell if none contain it "not ok" is the
result I'm looking for

Range of cells being compared is B10:B19 searching for the text "b3" as the
first two characters in the cells.

I've tried Vlookup nested with an if I just get errors, I've tried lookup
nested with if, I've spent about 2 hours searching various forms and have yet
to find a way to compare the entire range in a formula or two.

I'm hoping its just me screwing something up on the vlookup/lookup. But
everything I've tried so far either only for comparing vs 1 cell and not a
range, or just gives #value or #N/A as a result when comparing the range.

I'll be more then happy to rebuild any of the forumlas I have tried if
anyone thinks that might help.

Comparing the first 2 characters of a coloumn to a string
 

If I understand what you're wanting to do I think this will work:

=IF(COUNTIF(B10:B19,"b3*"),"OK","not OK")

--
Biff
Microsoft Excel MVP


"Jaime" wrote in message
...
Hi. I'm looking for a way to compare the first 2 charcters of a range of
cells in a column to a string and if any of the cells contain that string
to
drop an "ok" result into the formulas cell if none contain it "not ok" is
the
result I'm looking for

Range of cells being compared is B10:B19 searching for the text "b3" as
the
first two characters in the cells.

I've tried Vlookup nested with an if I just get errors, I've tried lookup
nested with if, I've spent about 2 hours searching various forms and have
yet
to find a way to compare the entire range in a formula or two.

I'm hoping its just me screwing something up on the vlookup/lookup. But
everything I've tried so far either only for comparing vs 1 cell and not a
range, or just gives #value or #N/A as a result when comparing the range.

I'll be more then happy to rebuild any of the forumlas I have tried if
anyone thinks that might help.