IF statement & Text entry

[brian.baker13]

I have a formula in a sheet
=IF(OR(A36="",A36<=0),"",VLOOKUP(A36,'CoO table'!$B$6:$G$999,5,0))

this returns a blank if a 0 or blank is in the corresponding cell and
does a VLOOKUP routine if other.

I want to include returning a blank if a text entry is in A36 It is
only numbers where I want to perform the VLOOKUP routine.

Kind Regards
Brian

 

[Niek Otten]

Hi Brian,

=IF(OR(A36="",A36<=0,ISTEXT(A36)),"",VLOOKUP(A36,'CoO table'!$B$6:$G$999,5,0))

Be careful, text that looks like a number will cause ""

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| I have a formula in a sheet
| =IF(OR(A36="",A36<=0),"",VLOOKUP(A36,'CoO table'!$B$6:$G$999,5,0))
|
| this returns a blank if a 0 or blank is in the corresponding cell and
| does a VLOOKUP routine if other.
|
| I want to include returning a blank if a text entry is in A36 It is
| only numbers where I want to perform the VLOOKUP routine.
|
| Kind Regards
| Brian

 

[Pete_UK]

Change it to this:

=IF(OR(A36="",A36<=0,ISTEXT(A36)),"",VLOOKUP(A36,'CoO table'!$B$6:$G
$999,5,0))

Or, in case you think of some more special conditions, try this:

=IF(ISNA(VLOOKUP(A36,'CoO table'!$B$6:$G$999,5,0)),"",VLOOKUP(A36,'CoO
table'!$B$6:$G$999,5,0))

i.e. if there are any errors in trying to do the lookup then return
"", otherwise do the lookup.

Hope this helps.

Pete