VLOOKUP error result - remove

[mitommy]

I am using a VLOOKUP to check information from different workbooks. The formula I've written is

=VLOOKUP(A7,'[TWOS RPT 2-07-05.xls]CHRONIC OFFENDERS'!$G$2:$H$70,2,FALSE)

Sometimes the results comes out as #N/A (this is the correct results).

Instead of that error I'd like to get a blank in the cell. What is the best way to accomplish that? Should I nest the VLOOKUP within an IF statement? If so, How?

 

[Brian from Maui]

I am using a VLOOKUP to check information from different workbooks. The formula I've written is

=VLOOKUP(A7,'[TWOS RPT 2-07-05.xls]CHRONIC OFFENDERS'!$G$2:$H$70,2,FALSE)

Sometimes the results comes out as #N/A (this is the correct results).

Instead of that error I'd like to get a blank in the cell. What is the best way to accomplish that? Should I nest the VLOOKUP within an IF statement? If so, How?

In an empty cell, enter your VLOOKUP formula. In the cell where the formula originally resided, enter,

=IF(ISNA(A1),"",A1)

Where A1 houses the VLOOKUP formula.

 

[Marbles]

You could hide the actual lookup column and have one next to it with this maybe:=IF(ISERROR(B1),"",B1).

Worth a try.

 

[Ron Morris]

Hi,

Another way that has gotten me out of a jam is:

=IF(ISNA(VLOOKUP(A7,'[TWOS RPT 2-07-05.xls]CHRONIC OFFENDERS'!$G$2:$H$70,2,FALSE)),"",VLOOKUP(A7,'[TWOS RPT 2-07-05.xls]CHRONIC OFFENDERS'!$G$2:$H$70,2,FALSE))

This way you don't have to reference another cell.

Ron