VLOOKUP's with blank cells

[dgavin]

I have a problem with this formula

VLOOKUP(A7,MyBets!$B$2:$F$500,4,0)="U"

when the result in A7 cannot be found in MyBets!$B$2:$F$500 i get N/A

is there a way to just give "blank" unless the A7 result is found ?

 

[Dannyh1]

Hi Try,

=IF(ISNA(VLOOKUP(A7,MyBets!$B$2:$F$500,4,0)),"","U")

 

[dgavin]

The full formula is

IF(A5="","",IF(AND(F5<=1.5,VLOOKUP(A5,MyBets!$B$2:$F$500,4,0)="U",$L$2<0,$S$1<0),Controls!$B$6,""))))

so do i change it to

IF(A5="","",IF(AND(F5<=1.5,ISNA(VLOOKUP(A5,MyBets!$B$2:$F$500,4,0),"","U",$L$2<0,$S$1<0),Controls!$B$6,""))))

thanks

 

[T. Valko]

I have a problem with this formula

VLOOKUP(A7,MyBets!$B$2:$F$500,4,0)="U"

when the result in A7 cannot be found in MyBets!$B$2:$F$500 i get N/A

is there a way to just give "blank" unless the A7 result is found ?

If you're using Excel 2007 or later...

=IFERROR(VLOOKUP(A7,MyBets!$B$2:$F$500,4,0),"")

Note that this will trap ALL errors, not just the #N/A error.