If 2 columns have #N/A
 

Hi All

I have 2 columns of data which both look at a Vlookup and return either an
answer or #N/A (no data match), see below
Bloggs 31 Eagle Street
#N/A #N/A
Smith #N/A
Michaels 21 Alpha Road
#N/A #N/A

What I want is an IF statement that says if either column is #N/A and
display a value (say 1). Then I can filter out all the non #N/A

Is this possible, I have tried various things but none seem to work.

Any help greatly appreciated

Kind regards

Rexmann

Using excel 2003

If 2 columns have #N/A
 

=--OR(ISNA(A2),ISNA(B2)) if you want 0 or 1
=OR(ISNA(A2),ISNA(B2)) if you're happy with TRUE or FALSE
--
David Biddulph

"rexmann" wrote in message
...
Hi All

I have 2 columns of data which both look at a Vlookup and return either an
answer or #N/A (no data match), see below
Bloggs 31 Eagle Street
#N/A #N/A
Smith #N/A
Michaels 21 Alpha Road
#N/A #N/A

What I want is an IF statement that says if either column is #N/A and
display a value (say 1). Then I can filter out all the non #N/A

Is this possible, I have tried various things but none seem to work.

Any help greatly appreciated

Kind regards

Rexmann

Using excel 2003

If 2 columns have #N/A
 

Hi

Thanks for that, job done perfectly

Cheers Rexmann

"David Biddulph" wrote:

=--OR(ISNA(A2),ISNA(B2)) if you want 0 or 1
=OR(ISNA(A2),ISNA(B2)) if you're happy with TRUE or FALSE
--
David Biddulph

"rexmann" wrote in message
...
Hi All

I have 2 columns of data which both look at a Vlookup and return either an
answer or #N/A (no data match), see below
Bloggs 31 Eagle Street
#N/A #N/A
Smith #N/A
Michaels 21 Alpha Road
#N/A #N/A

What I want is an IF statement that says if either column is #N/A and
display a value (say 1). Then I can filter out all the non #N/A

Is this possible, I have tried various things but none seem to work.

Any help greatly appreciated

Kind regards

Rexmann

Using excel 2003