Prev: look up, match index? multi variable formulas
Next: Replacing spaces with data from another cell
From: Gotroots on 15 Dec 2009 17:36
What formula is suitable to determine if a value in 'B' is not present in A:A

example:

oranges are in B3 but can not be found anywhere in A:A
From: Eva on 15 Dec 2009 17:45
for example
=IF(ISNA(VLOOKUP(B1,A:A,1,FALSE))=TRUE,"not found",VLOOKUP(B1,A:A,1,FALSE))
Click yes if helped
--
Greatly appreciated
Eva


"Gotroots" wrote:

> What formula is suitable to determine if a value in 'B' is not present in A:A
>
> example:
>
> oranges are in B3 but can not be found anywhere in A:A
From: T. Valko on 15 Dec 2009 17:58
If you're looking for a conditional formatting formula...

=ISNA(MATCH(B3,A:A,0))

Or...

=COUNTIF(A:A,B3)=0

--
Biff
Microsoft Excel MVP


"Gotroots" <Gotroots(a)discussions.microsoft.com> wrote in message
news:6B8859EC-2134-4110-95FF-E63258F1E6A9(a)microsoft.com...
> What formula is suitable to determine if a value in 'B' is not present in
> A:A
>
> example:
>
> oranges are in B3 but can not be found anywhere in A:A


From: gotroots on 16 Dec 2009 07:36
Thank you both for your input.

I have used instead the find unique option in excel 2007 conditional
formatting.

Cheers

"T. Valko" wrote:

> If you're looking for a conditional formatting formula...
>
> =ISNA(MATCH(B3,A:A,0))
>
> Or...
>
> =COUNTIF(A:A,B3)=0
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Gotroots" <Gotroots(a)discussions.microsoft.com> wrote in message
> news:6B8859EC-2134-4110-95FF-E63258F1E6A9(a)microsoft.com...
> > What formula is suitable to determine if a value in 'B' is not present in
> > A:A
> >
> > example:
> >
> > oranges are in B3 but can not be found anywhere in A:A
>
>
> .
>
 | 
Pages: 1
Prev: look up, match index? multi variable formulas
Next: Replacing spaces with data from another cell