Vlookup and #N/a

[Fishbone]

Hi all,

I am using the VLOOKUP formula to cross-reference two sheets. I kno
that a lot of my data from the first sheet wont be in the second sheet
so it will display #N/A.

I want the cell to returned blank or different text rather than #N/A.

Is there a way of doing this.

This is what I have tried...

=IF(VLOOKUP(A5,Rae2001!D5:I2203,6,0)="A","Active","")

but it still contains #n/a. I also tried combining it with th
'ISERROR' function but still no luck. Any ideas guys??

Thanks

Laurenc

 

[Frank Kabel]

Hi
one typical approach:
=IF(ISNA(VLOOKUP(A5,Rae2001!D5:I2203,6,0)),"No
match",VLOOKUP(A5,Rae2001!D5:I2203,6,0))

another would be
=IF(COUNTIF(Rae2001!D5:I2203,A5),VLOOKUP(A5,Rae2001!
D5:I2203,6,0),"no match")

 

[JulieD]

Hi Fishbone

i've answered you in newusers - its not normally necessary to post in
multiple groups.

Cheers
JulieD

 

[GregJG]

Hi,

I use a code simular to this to take away the #N/A and leave a blank
fill in between the " " if ya want a text if it turns out to be N/A

=IF(ISNA(VLOOKUP(A5,Rae2001!D5:I2203,6,FALSE)),
",VLOOKUP(A5,Rae2001!D5:I2203,6,FALSE)

 

[Fishbone]

Thanks Greg/Frank

Your formula worked a treat

I also apologise for the double posts but I thought I had deleted th
post in the 'new users' forum because I it seemed that most users wer
in 'misc' forum.


*Just checked the new users and there is a post which followed on fro
my original post, from Julie (Thanks to you for replying as wel
Julie)
Laurenc

 

[Aladin Akyurek]

If efficiency is of any concern, use a 2-cell approach...

C5:

=VLOOKUP(A5,Rae2001!$D$5:$I$2203,6,0)="A"

B5:

=IF(ISNA(C5),"","Active")

B5 is the result cell.

As a side note: Still hoping that MS extends the param list of lookup
functions such that:

=VLOOKUP(A5,Rae2001!$D$5:$I$2203,6,0,"")="A"

would be possible...